mysql

Contents

1. 1. 安装
   1. 1.0.0.1. MariaDB
   2. 1.0.0.2. mysql
   3. 1.0.0.3. 主主互备配置
2. 1.0.1. 导出数据
3. 1.0.2. 执行命令
4. 1.0.3. 备份数据库
5. 1.0.4. 恢复数据库
6. 1.0.5. 用户管理

2. 库表数据量查看

3. 禁用数据库-重命名

安装

MariaDB

https://mariadb.com/kb/en/yum/

vi /etc/yum.repos.d/MariaDB.repo

[mariadb]
name = MariaDB-10.3
baseurl=http://142.4.219.197/10.3/centos7-amd64
# alternative: baseurl=http://archive.mariadb.org/mariadb-10.3.14/yum/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=0

yum install MariaDB-server

service  mariadb  start 
sudo mysql_secure_installation

systemctl start mariadb #启动服务
systemctl enable mariadb #设置开机启动
systemctl restart mariadb #重新启动
systemctl stop mariadb.service #停止MariaDB

mysql

yum install mysql-server

sudo rpm -ivh mysql-community-common-5.7.38-1.el7.x86_64.rpm 
sudo rpm -ivh mysql-community-libs-5.7.38-1.el7.x86_64.rpm
sudo rpm -ivh mysql-community-client-5.7.38-1.el7.x86_64.rpm 
sudo rpm -ivh mysql-community-server-5.7.38-1.el7.x86_64.rpm 

sudo systemctl enable mysqld
sudo service mysqld start
sudo grep 'temporary password' /var/log/mysqld.log

mysql  -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

mysql>create user ds@localhost identified by '123';
mysql>grant all on *.* to ds@localhost identified by '123';
mysql>grant all privileges on *.* to ds@'%' ;
mysql>grant select on *.* to ds@'hostname' identified by '123' with grant option;
MariaDB [mysql]> select host,user,password from mysql.user;

mysql>flush privileges;

# ubuntu - mysql8
sudo apt install mysql-server
sudo cat /etc/mysql/debian.cnf #密码
mysql -u  debian-sys-maint -p
use mysql;
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '123456';
FLUSH PRIVILEGES;

sudo vi /etc/mysql/my.cnf

[mysqld]
bind-address=0.0.0.0

Public Key Retrieval is not allowed解决方法：

1. ALTER USER ‘root‘@’%’ IDENTIFIED WITH mysql_native_password BY ‘root’;
2. 连接url增加参数 ?allowPublicKeyRetrieval=true

https://www.cnblogs.com/river2005/p/6813618.html

主主互备配置

vi /etc/my.cnf   /etc/my.cnf.d/mariadb-server.cnf

server-id = 1           
auto-increment-offset = 1    
auto-increment-increment = 2  
log-bin = mysql-bin     
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed 
slave-skip-errors = all

server-id = 2        
auto-increment-offset = 2    
auto-increment-increment = 2    
log-bin = mysql-bin    
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed 
slave-skip-errors = all

#保存配置文件并重启mysql
service mariadb restart
sudo service mysqld restart

#用mysql控制台登录并授权从库可以读取主库的二进制日志
mysql -u root -p
mysql>grant replication slave,replication client on *.* to root@'192.168.56.108' identified by "root";
mysql>flush privileges;

#用mysql控制台登录并授权从库可以读取主库的二进制日志
mysql -u root -p
mysql>grant replication slave,replication client on *.* to root@'192.168.56.109' identified by "root";

mysql>flush privileges;

#查看下log bin日志和pos值位置，并记录File 和Position的值
mysql> show master status;

change  master to master_host='192.168.56.108',master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=373;   
start slave;
show slave status\G;
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes


change  master to master_host='192.168.56.109',master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=373;   
start slave;
show slave status\G;
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

#stop slave;
#flush logs;

====
create database test;

use test;

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

show tables;

INSERT INTO runoob_tbl
(runoob_title, runoob_author, submission_date)
VALUES
("学习 PHP", "菜鸟教程", NOW());


INSERT INTO runoob_tbl
(runoob_title, runoob_author, submission_date)
VALUES
("JAVA 教程", "RUNOOB.COM", '2016-05-06');


select * from runoob_tbl;

drop table runoob_tbl;
drop database test;

导出数据

mysql -u root -h 10.3.4.194 -p -e “select * from dolphinscheduler.t_ds_user;” > userinfo.log

执行命令

mysql -u root -h 10.3.4.194 -p -e “drop database dolphinscheduler;”

备份数据库

mysqldump -h10.3.4.194 -P3306 -uroot -p –databases dolphinscheduler > dolphinscheduler.sql;

恢复数据库

mysql -u root -h 10.3.4.194 -p < dolphinscheduler.sql

用户管理

create user 'admin'@'localhost' IDENTIFIED BY 'Admin123!';

GRANT  ALL PRIVILEGES  ON *.* TO 'admin'@'localhost'

ALTER USER 'demo'@'localhost' ACCOUNT UNLOCK;

ALTER USER 'backup_user'@'%' ACCOUNT LOCK;

ALTER USER 'root'@'%' IDENTIFIED BY 'vs jw';

DROP USER 'root'@'%';

库表数据量查看

-- 统计MySQL指定数据库中有多少张表
SELECT count(*) TABLES, table_schema FROM information_schema.TABLES
GROUP BY table_schema;
 
-- 统计MySQL指定数据库中表数据量
use ;
select table_name,table_rows 
from information_schema.tables 
where TABLE_SCHEMA = '数据库名称' 
order by table_rows desc;

禁用数据库-重命名

RENAME DATABASE old_database_name TO new_database_name;