Mariadb/Mysql 主从复制（1）
一、原理
mysql的主从数据同步是一个异步复制过程，需要master开启bin-log日志功能，bin-log记录了master库中的增、删、修改、更新操作的sql语句，整个过程需要开启3个线程，分别是master开启I/O线程，slave开启I/O线程和SQL线程
1、在slave服务器上执行start slave命令开启主从复制开关，主从复制开始进行，slave I/O线程会通过master创建的授权用户连接上master，并请求master从指定文件和位置之后发送bin-log日志内容
2、master接收请求后，master I/O线程更加slave发送的指定bin-log日志position点之后的内容，然后返回给slave的I/O线程；返回的信息中除了bin-log日志外，还有在master服务器记录的新的bin-log文件名及新的bin-log中的下一个指定更新位置（position）
3、slave I/O线程接收信息后，将接收的日志内容一次添加到slave端的relay-log（中继日志）文件（mysql-relay-bin.xxxxxx）的最末端，并将读取到的master端的bin-log文件名和position点记录到master.info文件中，以便下次读取时能够告知master从相应的bin-log文件名及最后一个position点开始发起请求
4、slave SQL 线程检测到relay-log中I/O线程新增加的内容有更新，会立即分析relay-log日志中的内容，将解析的sql语句按顺序在slave里执行，并记录应用中继日志的文件名及位置点在relay-log.info中，执行成功后slave库与master库数据保持一致
总结
主从复制是异步的逻辑的SQL语句级的复制
复制时，主库有一个I/O线程，从库有两个线程，I/O和SQL线程
作为复制的所有mysql节点server-id都不能相同
bin-log文件只记录对数据库有更改的sql语句（数据库内容的变更），不记录任何查询（select，slow）语句
原理流程图如下：
主从复制条件
开启binlog功能
主库要建立账号
从库要配置master.info
start slave 开启复制功能
二、环境
master：192.168.216.52
slave:192.168.216.53
mariadb版本10.2.24
[root@web2 ~]# rpm -qa Maria* MariaDB-server-10.2.24-1.el7.centos.x86_64 MariaDB-compat-10.2.24-1.el7.centos.x86_64 MariaDB-common-10.2.24-1.el7.centos.x86_64 MariaDB-client-10.2.24-1.el7.centos.x86_64 [root@web2 ~]#
三、安装配置
1、添加mariadb，yum源
[root@web2 ~]# cat /etc/yum.repos.d/mariadb.repo [mariadb] name = MariaDB baseurl = https://mirrors.ustc.edu.cn/mariadb/yum/10.2/centos7-amd64/ gpgkey=https://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB gpgcheck=1
2、这里yum安装
两台机器都安装
yum install mariadb-server
3、初始化mariadb，可以忽略
参考：https://www.cnblogs.com/zhangxingeng/p/10882845.html
4、配置master
-------------------修改配置文件（红色部分关键，其他为优化）
[root@web2 my.cnf.d]# cat server.cnf # # These groups are read by MariaDB server. # Use it for options that only the server (but not clients) should see # # See the examples of server my.cnf files in /usr/share/mysql/ # # this is read by the standalone daemon and embedded servers [server] # this is only for the mysqld standalone daemon [mysqld] server-id=1 log-bin=mysql-bin #binlog-do-db=liting #binlog-ignore-db=mysql sync_binlog=1 binlog_checksum = none binlog_format = mixed port = 3306 socket = /var/lib/mysql/mysql.sock skip-external-locking key_buffer_size = 256MB max_allowed_packet = 1MB table_open_cache = 256 sort_buffer_size = 1MB read_buffer_size = 1MB read_rnd_buffer_size = 4MB myisam_sort_buffer_size = 64MB thread_cache_size = 8 query_cache_size = 16MB thread_concurrency = 8 [mysqldump] quick max_allowed_packet = 16MB [mysql] no-auto-rehash [myisamchk] key_buffer_size = 128MB sort_buffer_size = 128MB read_buffer = 2MB write_buffer = 2MB [mysqlhotcopy] interactive-timeout # # * Galera-related settings # [galera] # Mandatory settings #wsrep_on=ON #wsrep_provider= #wsrep_cluster_address= #binlog_format=row #default_storage_engine=InnoDB #innodb_autoinc_lock_mode=2 # # Allow server to accept connections on all interfaces. # #bind-address=0.0.0.0 # # Optional setting #wsrep_slave_threads=1 #innodb_flush_log_at_trx_commit=0 # this is only for embedded server [embedded] # This group is only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] # This group is only read by MariaDB-10.2 servers. # If you use the same .cnf file for MariaDB of different versions, # use this group for options that older servers don't understand [mariadb-10.2] [root@web2 my.cnf.d]#
----------------------授权
grant replication slave,replication client on *.* to 'tongbu'@'%' identified by '123456';
----------------------查看bin-log及position点
MariaDB [test3]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 320| | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
5、配置slave
--------------------修改配置
[root@web3 my.cnf.d]# cat server.cnf # # These groups are read by MariaDB server. # Use it for options that only the server (but not clients) should see # # See the examples of server my.cnf files in /usr/share/mysql/ # # this is read by the standalone daemon and embedded servers [server] # this is only for the mysqld standalone daemon [mysqld] socket = /var/lib/mysql/mysql.sock port = 3306 skip-external-locking key_buffer_size =256MB max_allowed_packet = 1MB table_open_cache = 256 sort_buffer_size = 1MB read_buffer_size = 1MB read_rnd_buffer_size = 4MB myisam_sort_buffer_size = 64MB thread_cache_size = 8 query_cache_size = 16MB thread_concurrency = 8 server-id = 2 # # * Galera-related settings # [galera] # Mandatory settings #wsrep_on=ON #wsrep_provider= #wsrep_cluster_address= #binlog_format=row #default_storage_engine=InnoDB #innodb_autoinc_lock_mode=2 # # Allow server to accept connections on all interfaces. # #bind-address=0.0.0.0 # # Optional setting #wsrep_slave_threads=1 #innodb_flush_log_at_trx_commit=0 # this is only for embedded server [embedded] # This group is only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] # This group is only read by MariaDB-10.2 servers. # If you use the same .cnf file for MariaDB of different versions, # use this group for options that older servers don't understand [mariadb-10.2] [root@web3 my.cnf.d]#
[root@web3 my.cnf.d]# cat mysql-clients.cnf #[mysql][mysqlcheck][mysqldump]也可以写在这个文件里，master我是都卸载server.cnf里面了 # # These groups are read by MariaDB command-line tools # Use it for options that affect only one utility # [mysql] no-auto-rehash [mysql_upgrade] [mysqladmin] [mysqlbinlog] [mysqlcheck] key_buffer_size = 128MB sort_buffer_size = 128MB read_buffer = 2MB write_buffer = 2MB [mysqldump] quick max_allowed_packet = 16MB [mysqlimport] [mysqlshow] [mysqlslap] [mysqlhotcopy] interactive-timeout [root@web3 my.cnf.d]#
---------------------slave进入mysql设置
slave指定master ip、用户名、密码、bin-log文件名、position（下面标记红色部分）
[root@web3 my.cnf.d]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 12 Server version: 10.2.24-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> change master to master_host='192.168.216.52',master_user='tongbu',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=320;MariaDB [(none)]> master_host='192.168.216.52',master_user='tongbu',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=320;
MariaDB [(none)]> slave start;
---------------查看状态正常状态如下：（正常状态关注标记紫色部分)
MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.216.52 Master_User: tongbu Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 593 Relay_Log_File: web3-relay-bin.000006 Relay_Log_Pos: 828 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 593 Relay_Log_Space: 1136 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 1 row in set (0.00 sec)
需要关注如下几点
引用：https://www.cnblogs.com/kevingrace/p/5685511.html mysql主从同步(4)-Slave延迟状态监控
# 1）Slave_IO_Running：该参数可作为io_thread的监控项，Yes表示io_thread的和主库连接正常并能实施复制工作，No则说明与主库通讯异常，多数情况是由主从间网络引起的问题；
2）Slave_SQL_Running：该参数代表sql_thread是否正常，YES表示正常，NO表示执行失败，具体就是语句是否执行通过，常会遇到主键重复或是某个表不存在。
3）Seconds_Behind_Master：是通过比较sql_thread执行的event的timestamp和io_thread复制好的event的timestamp(简写为ts)进行比较，而得到的这么一个差值；
NULL—表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No，而非Yes。
0 — 该值为零，是我们极为渴望看到的情况，表示主从复制良好，可以认为lag不存在。
正值 — 表示主从已经出现延时，数字越大表示从库落后主库越多。
负值 — 几乎很少见，我只是听一些资深的DBA说见过，其实，这是一个BUG值，该参数是不支持负值的，也就是不应该出现。
6、测试，master创建一个test3的库及t1的表
[root@web2 my.cnf.d]# mysql -uroot -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 11 Server version: 10.2.24-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> create database test3; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> use test3; Database changed MariaDB [test3]> create table t1(id varchar(20),name varchar(20)); Query OK, 0 rows affected (0.02 sec) MariaDB [test3]> show tables; +-----------------+ | Tables_in_test3 | +-----------------+ | t1 | +-----------------+ 1 row in set (0.00 sec) MariaDB [test3]>
---------------slave查看已经同步过来了
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | test3 | +--------------------+ 5 rows in set (0.00 sec) MariaDB [(none)]>
转载请注明出处：https://www.cnblogs.com/zhangxingeng/p/10901452.html
