Mysql相关

赋权语句

grant all privileges on *.* to username@% identified by 'password';

grant all privileges on *.* to username@10.163.225.87 identified by 'password';

Mysql主从热备

锁表

FLUSH TABLES WITH READ LOCK;

dump Mysql数据

mysqldump -h192.168.17.21 -p3306 -uroot -p123123 --all-databases > /root/all_db.sql

解表

UNLOCK TABLES;

数据同步

CHANGE MASTER to MASTER_HOST="192.168.17.21",MASTER_PORT=3306,MASTER_user="root",MASTER_PASSWORD="123123",MASTER_LOG_FILE="mysql-bin.000001",MASTER_LOG_POS=154;

修改配置文件，选择要同步的数据库

vim /etc/mysql/mysql.conf.d/mysqld.cnf

server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size   = 100M
binlog_do_db            = hive
binlog_do_db            = ranger
binlog_do_db            = azkaban
binlog_ignore_db        = mysql
binlog_ignore_db        = performance_schema
binlog_ignore_db        = information_schema
binlog_ignore_db        = sys

设置mysql密码