mariadb/mysql主从复制
一、mysql的复制原理
至少需要两个节点，一主（master）一从（slave）
那么从节点如何从主节点获得数据呢？
1、主节点开启二进制日志，记录了修改数据的所有语句
2、从节点扮演mysql客户端请求主节点的二进制日志，默认是第一个文件的最开始位置，通常要指定二进制文件的指定位置。从节点收到二进制日志后保存在本地的中继日志中，并且会记录已经读取到哪个二进制日志的哪个位置，用作再次请求二进制日志的起始位置。从节点会读取中继日志中的事件完成重放后从而形成一模一样的数据保存在数据库中。
3、slave节点上负责从master节点上请求二进制日志的线程叫做IO_THREAD，从节点收到二进制日志后，保存在本地中继日志中并由SQL_THREAD做重放后将数据保存在数据库中。
4、主节点会为每个请求二进制日志的从服务器线程IO_THREAD启动一个响应线程dump_thread，该线程负责读取主服务器上的二进制日志并发送给从服务器。
二、mysql基于二进制日志主从复制特点：
1、异步复制
当客户端写入数据时，主节点记录完二进制日志便回复客户端已经写好了，而无需等待从节点写入完成才回复。因此，数据刚刚在主服务器上写入完成，而没有来得及同步到从节点的话，从服务器上是读取不到该数据的。
2、从节点可能会落后于主节点/主从数据不一致
有时候，落后也是有好处的，万一主服务器上误删了数据，从服务器上还是可以保留一段时间的。可以专门部署一台服务器落后于主节点的。
主节点上，事务是可以并行执行提交的，但是二进制日志还是只能串行写入的，而等到从节点过来读取二进制日志并完成重放以后，就必然是落后了。
根据经验：同一机房内，主从之间最多落后1秒，可以接受
问题一：从节点上的中继日志完成重放后需要保留吗？
不需要，SQL_THREAD把中继日志重放完了以后
问题二：从节点上需要保存二进制日志吗？
需要看情况而定
1、二进制日志是用来发给从服务器做事件重放使用，所以从服务器如果没有从服务器的话，是不需要保留二进制日志的，因为保存二进制日志会增加了IO压力。
2、如果mysql从服务器拥有自己的从服务器，这种架构就是mysql的级联复制
补充知识点
二进制日志的事件记录格式：
STATEMENT：只记录修改的语句，虽然节省空间但不推荐，极有可能导致数据不一致
ROW：记录被修改的行的数据，记录数据最精确，需要更多存储空间，推荐
MIXED：默认的记录格式，mysql自行判定以STATEMENT还是ROW的方式来记录，推荐
三、主从复制模型配置过程演示
主节点：开启二进制日志，设定全局唯一的SERVER_ID,创建拥有复制权限的用户账号(需要REPLICATION SLAVE,REPLICATION CLIENT)
从节点：开启中继日志，设定全局唯一的SERVER_ID,使用有复制权限的用户账号连接至主服务器并启动复制线程
node1: 172.16.92.1/16 mariadb主服务器
node2: 172.16.92.2/16 mariadb从服务器
以上节点均为CentOS 7.1
配置环境
1. 配置好光盘yum源
2. 关闭selinux和iptables
node1: mariadb主服务器 [root@node1 ~]# yum -y install mariadb-server [root@node1 ~]# vim /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd #######以下的内容为添加######## #二进制变更日志 log-bin=mysql-bin #二进制日志格式为混合模式 binlog_format=mixed #为主服务器node1的ID值 server-id = 1 port = 3306 skip-external-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M thread_concurrency = 4 innodb_file_per_table = on skip_name_resolve = on ############################### ###### 以下的内容可选 ######## [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout ############################# [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid # # include all files from the config directory # !includedir /etc/my.cnf.d ############### End for my.cnf ################# [root@node1 ~]# systemctl start mariadb [root@node1 ~]# mysql MariaDB [(none)]> grant replication client,replication slave on *.* to 'repluser'@'172.16.92.2' identified by 'replpass'; MariaDB [(none)]> flush privileges; MariaDB [(none)]> show master status\G *************************** 1. row *************************** File: mysql-bin.000003 Position: 497 Binlog_Do_DB: Binlog_Ignore_DB: ##### 记下mysql-bin.000003 和 497 , 设置从服务器中继日志时有用 #### node2: mariadb从服务器 [root@node2 ~]# yum -y install mariadb-server [root@node2 ~]# vim /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd ########## 添加以下内容 ########## log-bin=mysql-bin binlog_format=mixed server-id = 2 relay-log = relay-bin log_slave_updates = 1 read_only = on port = 3306 skip-external-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M thread_concurrency = 4 innodb_file_per_table = on skip_name_resolve = on ################################### ######### 以下内容可选 ############ [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout #################################### [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid # # include all files from the config directory # !includedir /etc/my.cnf.d ############# End of my.cnf ############### [root@node2 ~]# systemctl start mariadb [root@node2 ~]# mysql MariaDB [(none)]> show global variables like '%read_only%'; | read_only | ON | MariaDB [(none)]> show global variables like '%read_only%'\G *************************** 1. row *************************** Variable_name: read_only Value: ON MariaDB [(none)]> change master to master_host='172.16.92.1',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000003',master_log_pos=497,master_connect_retry=5,master_heartbeat_period=2; MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 172.16.92.1 Master_User: repluser Master_Port: 3306 Connect_Retry: 5 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 497 Relay_Log_File: relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: No Slave_SQL_Running: No 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: 497 Relay_Log_Space: 245 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: NULL 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: 0 MariaDB [(none)]> start slave; MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.92.1 Master_User: repluser Master_Port: 3306 Connect_Retry: 5 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 497 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 529 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes ........ 其余信息略 ........ MariaDB [(none)]> show processlist\G *************************** 3. row *************************** Id: 4 User: system user Host: db: NULL Command: Connect Time: 144 State: Slave has read all relay log; waiting for the slave I/O thread to update it Info: NULL Progress: 0.000 #说明: 从节点已经接收到所有的中继日志 node1 主节点上可查看到此进程 MariaDB [(none)]> show processlist\G *************************** 2. row *************************** Id: 4 User: repluser Host: 172.16.92.2:56821 db: NULL Command: Binlog Dump Time: 212 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL Progress: 0.000 在主节点上创建数据库测试是否能主从同步 MariaDB [(none)]> create database testdb; 在从节点上可看到testdb数据库, 说明主从同步成功! MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | testdb | +--------------------+ 再来看一下 从节点 的状态 MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.92.1 Master_User: repluser Master_Port: 3306 Connect_Retry: 5 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 584 #497->584 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 616 #529->616 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes ############# mysql主从复制结束 ############## 复制过滤器 (1) 基于库的白名单的实现 在从节点上设置 MariaDB [(none)]> stop slave; MariaDB [(none)]> set global replicate_do_db='testdb'; MariaDB [(none)]> start slave; MariaDB [(none)]> show global variables like '%replicat%'; | replicate_do_db | testdb | #只同步该数据库的数据 在主节点上创建新数据库, 查看是否能同步过来 MariaDB [(none)]> create database mydb; 从节点上并未看到mydb数据库 MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | testdb | +--------------------+ 在主节点的testdb中插入数据, 查看是否能同步过来 MariaDB [(none)]> use testdb; MariaDB [testdb]> create table t1(id int); MariaDB [testdb]> desc t1\G *************************** 1. row *************************** Field: id Type: int(11) Null: YES Key: Default: NULL Extra: 从节点上查看一样的数据, 说明从服务器现在只能同步一个数据库 MariaDB [(none)]> use testdb; MariaDB [testdb]> show tables\G *************************** 1. row *************************** Tables_in_testdb: t1 MariaDB [testdb]> desc t1\G *************************** 1. row *************************** Field: id Type: int(11) Null: YES Key: Default: NULL Extra:
