mysql主从复制

mysql主从

mysql主从复制配置

查看主库有哪些库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| teacher            |
+--------------------+
5 rows in set (0.000 sec)



查看从库有哪些库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.002 sec)



全备主库
MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.000 sec)


备份主库并将备份文件传送到从库
[root@master ~]# mysqldump -uroot -pyyz123! --all-databases > /opt/all-$(date "+%Y%m%d").sql
[root@master ~]# ls /opt/
all-20220801.sql
[root@master ~]# scp /opt/all-20220801.sql  root@192.168.17.134:/opt/
The authenticity of host '192.168.17.134 (192.168.17.134)' can't be established.
ECDSA key fingerprint is SHA256:FgHTcISusAncwNVY+cahQO+1WC5fFP/AY/3D/wHP5q8.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.17.134' (ECDSA) to the list of known hosts.
root@192.168.17.134's password: 
Permission denied, please try again.
root@192.168.17.134's password: 
all-20220801.sql                                             100%  467KB 133.0MB/s   00:00 



在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@slave ~]# mysql -uroot -pyyz123! < /opt/all-20220801.sql 
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| teacher            |
+--------------------+
5 rows in set (0.000 sec)



在主数据库里创建一个同步账号授权给从数据库使用
MariaDB [(none)]> create user 'yqz'@'192.168.17.134' identified by 'yqz123';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> grant replication slave on *.* to 'yqz'@'192.168.17.134';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)





配置主数据库
[root@master ~]# cd /etc/my.cnf.d/
[root@master my.cnf.d]# ls
auth_gssapi.cnf  enable_encryption.preset  mariadb-server.cnf  oqgraph.cnf
client.cnf       galera.cnf                mysql-clients.cnf
[root@master my.cnf.d]# vim mariadb-server.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=mysql-bin    启用binlog日志
server-id=10         数据库服务器唯一标识符，主库的server-id值必须比从库的大
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid



重启mariadb服务
[root@master ~]# systemctl restart mariadb
[root@master ~]# ss -anlt
State     Recv-Q     Send-Q         Local Address:Port         Peer Address:Port    Process    
LISTEN    0          80                   0.0.0.0:3306              0.0.0.0:*                  
LISTEN    0          128                  0.0.0.0:22                0.0.0.0:*                  
LISTEN    0          128                     [::]:22                   [::]:*          
    
    
查看主库的状态
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)





配置从数据库
[root@slave ~]# cd /etc/my.cnf.d/
[root@slave my.cnf.d]# ls
auth_gssapi.cnf  enable_encryption.preset  mariadb-server.cnf  oqgraph.cnf
client.cnf       galera.cnf                mysql-clients.cnf
[root@slave my.cnf.d]# vim mariadb-server.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=20
relay-log=mysql-relay-bin
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid


重启从库的mariadb服务
[root@slave ~]# systemctl restart mariadb
[root@slave ~]# ss -anlt
State     Recv-Q     Send-Q         Local Address:Port         Peer Address:Port    Process    
LISTEN    0          80                   0.0.0.0:3306              0.0.0.0:*                  
LISTEN    0          128                  0.0.0.0:22                0.0.0.0:*                  
LISTEN    0          128                     [::]:22                   [::]:*           
    

    
    
   
    
配置并启动主从复制
MariaDB [(none)]> change master to
    -> master_host='192.168.17.133',
    -> master_user='yqz',
    -> master_password='yqz123',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=328;
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)



查看从服务器状态
MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
                Slave_IO_State: Connecting to master
                   Master_Host: 192.168.17.133
                   Master_User: yqz
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 328
                Relay_Log_File: mysql-relay-bin.000001
                 Relay_Log_Pos: 4
         Relay_Master_Log_File: mysql-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
..........




测试验证
在主服务器的student库的yyzz表中插入数据
MariaDB [student]> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| yyzz              |
+-------------------+
1 row in set (0.000 sec)

MariaDB [student]> insert into yyzz values (1,'jack',20),(2,'tom',24),(3,'jerry',30);
Query OK, 3 rows affected (0.002 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [student]> select * from yyzz;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | jack  |   20 |
|  2 | tom   |   24 |
|  3 | jerry |   30 |
+----+-------+------+
3 rows in set (0.000 sec)


在从数据库中查看数据是否同步：
MariaDB [student]> select * from yyzz;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | jack  |   20 |
|  2 | tom   |   24 |
|  3 | jerry |   30 |
+----+-------+------+
3 rows in set (0.000 sec)

MariaDB [student]> exit
Bye
[root@slave ~]# 

GTID主从复制

主库配置,配置etc下的my.cnf文件,然后重启mysql
[root@master ~]# vim /etc/my.cnf
log-bin=mysql_bin
server-id=10
gtid_mode=on
enforce-gtid-consistency=true
log-slave-updates=on
[root@master ~]# systemctl restart mysqld


从库配置,配置etc下的my.cnf文件,然后重启mysql
[root@localhost ~]# vim /etc/my.cnf 
server-id=20
relay-log=myrelay
gtid_mode=on
enforce-gtid-consistency=true
log-slave-updates=on
read_only=on
master-info-repository=TABLE
relay-log-info-repository=TABLE
[root@localhost ~]# systemctl restart mysqld




登录mysql,添加用于同步的数据库账号
mysql> create user 'yyz'@'%' identified by 'yyz1234!';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'yyz'@'%';
Query OK, 0 rows affected (0.00 sec)

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

mysql> exit
Bye




登录mysql,配置主从同步
mysql> change master to
    -> master_host='192.168.17.133',    //主库IP
    -> master_port=3306,           		//默认端口
    -> master_user='yyz',               //创建的账户
    -> master_password='yyz1234!',      //密码
    -> 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;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.17.133
                  Master_User: yyz
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 745
               Relay_Log_File: myrelay.000005
                Relay_Log_Pos: 958
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
............




测试
在主库创建一个数据库
mysql> create database yyyz;
Query OK, 1 row affected (0.00 sec)


在从库查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yyyz               |
+--------------------+
5 rows in set (0.00 sec)







错误事项
Last_IO_Error: 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.
这个情况是两台主机具有相同的UUID
mysql> show variables like 'datadir';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| datadir       | /opt/data/ |
+---------------+------------+
1 row in set (0.00 sec)

mysql> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| d355ba0c-1200-11ed-9d5c-000c29b4a331 |
+--------------------------------------+
1 row in set (0.00 sec)


解决办法
[root@slave data]# vim auto.cnf 
[root@slave data]# rm -rf auto.cnf 
[root@slave ~]# systemctl restart mysqld

传统主从原理

主库开启binlog功能并授权从库连接主库，从库通过change master得到主库的相关同步信息,然后连接主库进行验证，主库IO线程根据从库slave线程的请求，从master.info开始记录的位置点向下开始取信息，同时把取到的位置点和最新的位置与binlog信息一同发给从库IO线程，从库将相关的sql语句存放在relay-log里面，最终从库的sql线程将relay-log里的sql语句应用到从库上，至此整个同步过程完成，之后将是无限重复上述过程。

GTID主从原理

• 当一个事务在主库端执行并提交时，产生GTID，一同记录到binlog日志中。
• binlog传输到slave,并存储到slave的relaylog后，读取这个GTID的这个值设置gtid_next变量，即告诉Slave，下一个要执行的GTID值。
• sql线程从relay log中获取GTID，然后对比slave端的binlog是否有该GTID。
• 如果有记录，说明该GTID的事务已经执行，slave会忽略。
• 如果没有记录，slave就会执行该GTID事务，并记录该GTID到自身的binlog，在读取执行事务前会先检查其他session持有该GTID，确保不被重复执行。
• 在解析过程中会判断是否有主键，如果有就用二级索引，如果没有就用全部扫描。