MySQL主从复制
简介:本次搭建实在多实例环境下搭建,mysql是5.7版本
架构图:
准备环境:
ip地址 | MySQL数据库 | |
db01 | 10.0.0.51 |
systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
netstat -tulnp
#数据库7 mysql -S /tmp/mysql3307.sock -e "select @@server_id"; +-------------+ | @@server_id | +-------------+ | 7 | +-------------+ #数据库8 mysql -S /tmp/mysql3308.sock -e "select @@server_id"; +-------------+ | @@server_id | +-------------+ | 8 | +-------------+ #数据库9 mysql -S /tmp/mysql3309.sock -e "select @@server_id"; +-------------+ | @@server_id | +-------------+ | 9 | +-------------+
mysql -S /tmp/mysql3307.sock -e "select @@log_bin"; +-----------+ | @@log_bin | +-----------+ | 1 | +-----------+
mysql -S /tmp/mysql3307.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123'"; mysql -S /tmp/mysql3307.sock -e "select user,host from mysql.user"
mysqldump -S /tmp/mysql3307.sock -A --master-data=2 --single-transaction >/tmp/all.sql mysql -S /tmp/mysql3308.sock </tmp/all.sql mysql -S /tmp/mysql3309.sock </tmp/all.sql
help change master to CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_PORT=3307, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=444, MASTER_CONNECT_RETRY=10;
[root@db01 ~]# grep "\-- CHANGE MASTER TO" /tmp/all.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=444; [root@db01 ~]# mysql -S /tmp/mysql3308.sock CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_PORT=3307, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=444, MASTER_CONNECT_RETRY=10; [root@db01 ~]# mysql -S /tmp/mysql3309.sock CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_PORT=3307, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=444, MASTER_CONNECT_RETRY=10;
[root@db01 ~]# mysql -S /tmp/mysql3308.sock start slave; [root@db01 ~]# mysql -S /tmp/mysql3309.sock start slave;
[root@db01 ~]# mysql -S /tmp/mysql3308.sock -e "show slave status\G"|grep Running: Slave_IO_Running: Yes Slave_SQL_Running: Yes [root@db01 ~]# mysql -S /tmp/mysql3309.sock -e "show slave status\G"|grep Running: Slave_IO_Running: Yes Slave_SQL_Running: Yes [root@db01 ~]#
注:如果搭建不成,可以执行以下命令,从3.1-3.8步骤重新来过。(没问题别整!!!!!)
[root@db01 ~]# mysql -S /tmp/mysql3308.sock -e "stop slave;reset slave all;" [root@db01 ~]# mysql -S /tmp/mysql3309.sock -e "stop slave;reset slave all;" #关闭在重启M.info里面的主库信息就没有了