Mysql_运维一主一从案例(2)

一、环境要求
（一）关闭防火墙和selinux

[root@localhost ~]#  systemctl stop firewalld.service
[root@localhost ~]#  setenforce 0

（二）配置域名解析

[root@localhost ~]# hostnamectl set-hostname master
[root@localhost ~]# init 6
[root@localhost ~]# hostnamectl set-hostname slave
[root@localhost ~]# init 6
[root@master ~]# tail -2 /etc/hosts
192.168.163.128 slave
192.168.163.135 master

二、主Master      

（一）准备数据                                

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> create database Daidai;
Query OK, 1 row affected (0.00 sec)

mysql> create table Daidai.t1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into Daidai.t1 values (1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from Daidia.t1;
ERROR 1146 (42S02): Table 'Daidia.t1' doesn't exist
mysql> select * from Daidai.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> \q
Bye

(二)启动二进制日志，服务器ID，GTID

[root@master ~]# vim /etc/my.cnf[mysqld]
...
[mysqld]
log_bin
server-id=1
gtid_mode=ON
enforce_gtid_consistency=1
...

[root@master ~]# systemctl restart mysqld

（三）创建复制用户 

[root@master ~]# mysql -uroot -p'Daidai@123'
 
mysql> grant replication slave,replication client on *.* to 'rep'@'%' identified by 'Daidai@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

（四）备份Master数据库的数据

[root@master ~]# mysqldump -p'Daidai@123' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F-%H`-all.sql

[root@master ~]# ls
2022-12-12-11-all.sql  

#将日志拷贝至从机
[root@master ~]# scp -r 2022-12-12-11-all.sql slave:/tmp
2022-12-12-11-all.sql   100%  856KB  33.0MB/s   00:00

（五）准备数据（验证主从是否同步）

[root@master ~]# mysql -uroot -p'Daidai@123'

mysql> insert into Daidai.t1 values (10),(9),(8);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from Daidai.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   10 |
|    9 |
|    8 |
+------+
6 rows in set (0.00 sec)

mysql> \q
Bye

三、从Slave

（一）测试rep用户是否可用

[root@slave ~]# mysql -urep -p'Daidai@123' -h master
mysql> \q
Bye

(二)启动二进制日志，服务器ID，GTID

[root@slave ~]# vim /etc/my.cnf

...
[mysqld]
log_bin
server-id=2
gtid_mode=ON
enforce_gtid_consistency=1
...

[root@slave ~]# systemctl restart mysqld

 （三）手动同步数据

[root@slave ~]# mysql -uroot -p'Daidai@123'

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source /tmp/2022-12-12-11-all.sql 
...
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

...

（四）设置主服务器

mysql> change master to
    -> master_host='master',
    -> master_user='rep',
    -> master_password='Daidai@123',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

 （五）启动从服务器

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

（六）查看启动状态

mysql> show slave status\G;

...
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 630
        Relay_Master_Log_File: master-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

 （七）查看数据是否同步

mysql> select*from Daidai.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   10 |
|    9 |
|    8 |
+------+
6 rows in set (0.00 sec)