作为Master服务器apenglinux-001.cn的配置 /* -- 建库，表，备份库，将备份传给另一台机器-- */ [root@apenglinux-001 ~]# mysql -uroot -p123456 -e "create database db1;use db1;create table t1(id int unsigned not null primary key auto_increment,name varchar(100));insert into t1(name)values('zhangsan'),('lisi'),('wangwu');select * from t1;" mysql: [Warning] Using a password on the command line interface can be insecure. +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | | 3 | wangwu | +----+----------+

[root@apenglinux-001 ~]# mysqldump -uroot -p123456 -B db1 > db1_all.sql

[root@apenglinux-001 ~]# mysqldump -uroot -p123456 -B db1 > db1_all.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@apenglinux-001 ~]# scp db1_all.sql 192.168.1.20:/root The authenticity of host '192.168.1.20 (192.168.1.20)' can't be established. ECDSA key fingerprint is SHA256:ENfUT65MBnG5u82/aeA84Wl7klhZZMS/MI1+36eGu8k. ECDSA key fingerprint is MD5:bb:7a:dc:8b:d2:5b:99:54:9a:8d:f2:17:81:0a:5e:72. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.1.20' (ECDSA) to the list of known hosts. root@192.168.1.20's password: db1_all.sql 100% 2024 856.5KB/s 00:00

/* -- 配置my.cnf-- */ [root@apenglinux-001 ~]# vim /etc/my.cnf log-bin=apenglinux-001.cn server-id=100 binlog-do-db=db1 binlog-ignore-db=mysql

/* -- 开启MySQL服务-- */ [root@apenglinux-001 ~]# systemctl restart mysqld

/-- 作为master时，授权，将表锁定--/ [root@apenglinux-001 ~]# mysql -uroot -p123456 -e "grant replication slave on . to slave@192.168.1.20 identified by '123';flush tables with read lock;"

作为slave服务器apenglinux-002的配置

/-- 还原从master上传过来的数据库-- / [root@apenglinux-002 ~]# mysql -uroot -p123456 < db1_all.sql / -- 配置my.cnf-- / validate_password=off log-bin=apenglinux-002.cn server-id=90 binlog-do-db=db1 binlog-ignore-db=mysql /-- 重启mysql--/ [root@apenglinux-002 ~]# systemctl restart mysqld /* -- 停止slave,指定主服务器的ip,user,password,log_file,log_pos,开启slave-- / [root@apenglinux-002 ~]# mysql -uroot -p123456 -e "stop slave;change master to master_host='192.168.1.10',master_port=3306,master_user='slave',master_password='123',master_log_file='apenglinux-001.000001',master_log_pos=449;start slave;" / -- 去主服务器上开启解表操作 -- / [root@apenglinux-001 ~]# mysql -uroot -p123456 -e 'unlock tables;' / -- 查看slave的状态 -- / [root@apenglinux-002 ~]# mysql -uroot -p123456 -e 'show slave status\G' Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 Last_IO_Errno: 0 Last_SQL_Errno: 0 作为master服务器apenglinux-002.cn的配置 [root@apenglinux-002 ~]# mysql -uroot -p123456 -e 'grant replication slave on . to slave@192.168.1.10 identified by "123";flush tables with read lock;show master status;' mysql: [Warning] Using a password on the command line interface can be insecure. +-----------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-----------------------+----------+--------------+------------------+-------------------+ | apenglinux-002.000001 | 449 | db1 | mysql | | +-----------------------+----------+--------------+------------------+-------------------+ 作为slave服务器apenglinux-001.cn的配置 [root@apenglinux-001 ~]# mysql -uroot -p123456 -e 'stop slave;change master to master_host="192.168.1.20",master_port=3306,master_user="slave",master_password="123",master_log_file="apenglinux-002.000001",master_log_pos=449;start slave;' 去apenglinux-002.cn 上解锁 [root@apenglinux-002 ~]# mysql -uroot -p123456 -e 'unlock tables;' / -- 查看slave的状态 --/ [root@apenglinux-001 ~]# mysql -uroot -p123456 -e 'show slave status\G' Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 Last_IO_Errno: 0 Last_SQL_Errno: 0 此时 apenglinux-001与apenglinux-002两台机器互为主从了。 测试： / -- 在apenglinux-001.cn上增加一条记录，在apenglinux-002 上查看-- */ [root@apenglinux-001 ~]# mysql -uroot -p123456 -e 'insert into db1.t1(name)values("aa");select * from db1.t1;' mysql: [Warning] Using a password on the command line interface can be insecure. +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | | 3 | wangwu | | 4 | aa | +----+----------+

[root@apenglinux-002 ~]# mysql -uroot -p123456 -e 'select * from db1.t1;' mysql: [Warning] Using a password on the command line interface can be insecure. +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | | 3 | wangwu | | 4 | aa | +----+----------+

/* -- 在apenglinux-002.cn上删除两条记录，在apenglinux-001上查看-- */ [root@apenglinux-002 ~]# mysql -uroot -p123456 -e 'delete from db1.t1 limit 2;select * from db1.t1;' mysql: [Warning] Using a password on the command line interface can be insecure. +----+--------+ | id | name | +----+--------+ | 3 | wangwu | | 4 | aa | +----+--------+

[root@apenglinux-001 ~]# mysql -uroot -p123456 -e 'select * from db1.t1;' mysql: [Warning] Using a password on the command line interface can be insecure. +----+--------+ | id | name | +----+--------+ | 3 | wangwu | | 4 | aa | +----+--------+