MySql(docker)-使用-给运行中的mysql 增加从库

基础命令收集

-- 主从同步-从库跳过一个错误事件
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;   
start slave;

-- run 主库
docker run -d --restart always --name=test-master-mysql -p 9996:3306 -e MYSQL_ROOT_PASSWORD=pwd -v /home/tm/conf/my.cnf:/etc/mysql/my.cnf  -v /home/tm/data:/var/lib/mysql -v /etc/localtime:/etc/localtime --cpus 2 mysql:5.7 --character-set-server=utf8 --collation-server=utf8_unicode_ci --lower-case-table-names=1;

-- run 从库
docker run -d --restart always --name=test-slave-mysql -p 9997:3306 -e MYSQL_ROOT_PASSWORD=pwd -v /home/tslave/conf/my.cnf:/etc/mysql/my.cnf  -v /home/tslave/data:/var/lib/mysql -v /etc/localtime:/etc/localtime --cpus 2 mysql:5.7 --character-set-server=utf8 --collation-server=utf8_unicode_ci --lower-case-table-names=1;

mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers>/tmp/full.sql


-- 导出主库备份sql文件
- 这个有问题
docker exec -it test-master-mysql mysqldump -uroot -ppwd -A --master-data=2 --single-transaction -R -E --triggers > /home/kdata/full.sql

- 这个没问题
docker exec test-master-mysql /usr/bin/mysqldump -u root --password=pwd --skip-lock-tables --single-transaction --flush-logs --master-data=2 --all-databases > /home/kdata/full.sql

复制进容器内，然后导入-导入到从库中 需要输入密码

cat /home/kdata/full.sql | docker exec -i test-slave-mysql /usr/bin/mysql -u root --password=pwd test

-- 以下无用
docker cp ./full.sql test-slave-mysql:/full.sql

docker exec -it test-slave-mysql mysql -uroot -ppwd -e "source /full.sql;"

docker exec -i test-slave-mysql sh -c 'mysql -uroot -ppwd < /full.sql'

docker exec -i test-slave-mysql mysql -uroot --password=pwd -ptest < ./full.sql

找到并导入的point

head /home/kdata/full.sql -n80 | grep "MASTER_LOG_FILE"
head /home/kdata/full.sql -n80 | grep "MASTER_LOG_POS"   -- 和上面一个意思

head /home/kdata/full.sql -n80 | grep "MASTER_LOG_FILE"
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=154;

head /home/bkmdata/full.sql -n80 | grep "MASTER_LOG_FILE"

给主库增加主从同步的权限

#创建slave用户 密码 slave
CREATE USER slave IDENTIFIED BY 'slave';
#赋予查询及复制权限
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%'; 
#赋予所有权限 不需要
-- GRANT ALL PRIVILEGES ON *.* TO 'slave'@'%' ; 
#刷新权限
FLUSH PRIVILEGES;

增加从库的配置

docker exec -it ticket-slave mysql -uroot -ppwd;
 -- 127.0.0.1 是mysql服务器的内网地址
 
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3333, MASTER_USER='repl', MASTER_PASSWORD='slave', MASTER_LOG_FILE='mysql-bin.000018', MASTER_LOG_POS=154; 
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=9996, MASTER_USER='slave', MASTER_PASSWORD='slave', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=154; 
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3356, MASTER_USER='slave', MASTER_PASSWORD='slave', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=154; 

CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3356, MASTER_USER='slave', MASTER_PASSWORD='slave', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=154; 
START SLAVE;
SHOW SLAVE STATUS\G;       -- \G用来代替";"，能把查询结果按键值的方式显示