3307备份:
————191为主,142为从步骤—————————
1、191先锁住数据库
FLUSH TABLES WITH READ LOCK;
2、执行show master status,并记录显示的内容
3、把191下的mysql/data1打包
zip -r data1.zip ./data1
4、解锁数据库
UNLOCK TABLES;
5、把191下打包后的mysql/data1.zip传到142上
142上执行:
scp -r root@10.132.44.235:/alidata/server/mysql/data1.zip /alidata/server/mysql/data1.zip
6、142停止mysql
7、把142原先的mysql/data1删除(备份),解压data1.zip并修改目录权限
unzip -d ./ data1.zip
chown -R mysql:mysql data1
chmod -R 755 data1
8、删除data1/auto.cnf
9、重启数据库
cd /alidata/server/mysql/bin/
mysqld_multi --defaults-extra-file=/alidata/opt/my_multi.cnf start
10、连接数据库,建立191连接:
CHANGE MASTER TO MASTER_HOST='10.132.44.23',
MASTER_PORT=3307,
MASTER_USER='xxx',
MASTER_PASSWORD='xxxx',
MASTER_LOG_FILE='mysql-bin.016129',
MASTER_LOG_POS=683211924;
11、开启同步
start slave;
12、查看是否同步
show slave status;
——————20180512 185当主,142当备——————
1、185:FLUSH TABLES WITH READ LOCK;
2、执行show master status,并记录显示的内容
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.016220 | 137655 | | | |
+------------------+----------+--------------+------------------+-------------------+
3、mysql/data1打包(这个包不要删,防止以后重新做备份可以直接用这个包)
zip -r data1.zip ./data1
4、解锁数据库
UNLOCK TABLES;
5、把185下打包后的mysql/data1.zip传到142上
142上执行:
scp -r root@10.160.35.234:/alidata/server/mysql/data1.zip /alidata/server/mysql/data1.zip
6、142停止mysql
7、把142原先的mysql/data1删除(备份),解压data1.zip并修改目录权限
unzip -d ./ data1.zip
chown -R mysql:mysql data1
chmod -R 755 data1
8、删除data1/auto.cnf
9、重启数据库
cd /alidata/server/mysql/bin/
mysqld_multi --defaults-extra-file=/alidata/opt/my_multi.cnf start
10、连接数据库,建立185连接:
CHANGE MASTER TO MASTER_HOST='10.160.35.23',
MASTER_PORT=3307,
MASTER_USER='replication',
MASTER_PASSWORD='xxx',
MASTER_LOG_FILE='mysql-bin.016220',
MASTER_LOG_POS=137655;
11、开启同步
start slave;
12、查看是否同步
show slave status;
13、出现如下错误:
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
原因:142的my_multi.cnf配置中servier_id=7跟185中的一样造成的,把142的改成10再重启数据库就好了,参与下面文章:
https://blog.csdn.net/cug_jiang126com/article/details/46846031