MariaDB、MySQL数据库主从同步

1、MySQL主从同步异步概念

成都创新互联公司专注于企业网络营销推广、网站重做改版、新乡县网站定制设计、自适应品牌网站建设、H5网站设计、购物商城网站建设、集团公司官网建设、成都外贸网站建设、高端网站制作、响应式网页设计等建站业务，价格优惠性价比高，为新乡县等各大城市提供网站开发制作服务。

    异步：主服务器写完日志后立即返回同步完成消息，不受从服务器的状态和影响，mysql默认为异步工作模式

    同步：主服务器写入数据到本地磁盘并记录好二进制日志，然后等从服务器发现数据发送改变再把数据同步到从服务器的日志中继并保存到二进制日志和磁盘，最后返回给主服务器同步完成的结果，因此比较影响性能。

2、同步过程：

    主 服务器在可能修改数据时会把行内容记录在二进制日志中，而从服务会不停到主服务器请求数据，相当于mysql客户端，不停请求服务器的3306端口，从服 务器请求到主服务器的日志先记录到从服务器的中级日志下来，然后从服务器的本地线程读取下来应用一次保存到从服务器的本地磁盘空间，从而达到与主服务器的 内容一致的效果。

SLAVE:IO thread：向主服务器请求二进制日志中的事件

SQL thread：从中继日志读取事件并在本地执行

MASTER:binglog dump：将IO thread的请求事件发送给对方

3、注意事项：

1、从服务器一定不能写数据，因为数据不会传输到主服务器

2、主服务器有并且只能有一个主服务器，一般不支持一丛多主

3、mariadb-10 支持多主模型，即支持多源复制（mutil-source replication）

4、主从版本尽量相同，或从版本低于主版本

5、主从的server-id不能相同

4、配置过程：

    1、主服务器：

        1、改server-id

        2、启用二进制日志

        3、创建有复制权限的账号

  2、从服务器

    1、改server-id

    2、启用中继日志

    3、指向主服务器-使用主服务器创建的账号和密码

    4、启动复制进程

5、环境：

系统：CentOS 6.5_x86_64

主IP：192.168.10.204

从IP：192.168.10.205

MariaDB版本：mariadb-10.0.15-linux-x86_64.tar.gz

一.新建主从结构，即主服务器没有任何数据的情况下加入从服务器：

主服务器：

 1、改server-id

    [root@node4 binlogs]# vim /etc/mysql/my.cnf

        server-id       = 10

  2、启用二进制日志

    log-bin=/data/binlogs/master-log

 3、创建有复制权限的账号

      mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.%.%' IDENTIFIED BY '123456';  

   Query OK, 0 rows affected (0.03 sec)

    mysql> FLUSH PRIVILEGES; #刷新表

    Query OK, 0 rows affected (0.00 sec)

  4、重启服务：

    [root@node4 binlogs]# /etc/init.d/mysqld  restart

    Shutting down MySQL..                                      [  OK  ]

    Starting MySQL.                                            [  OK  ]

从服务器：

    1、改server-id:

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

    server-id       = 100

    2、关闭从服务器的二进制日志：

    #log-bin=/data/binlogs/master-bin

   relay-log = /data/relaylogs/relay-logs #指定从服务器的日志存放路径

  3、重启服务：    [root@node5 relaylogs]# /etc/init.d/mysqld  restart

    Shutting down MySQL..                                      [  OK  ]

    Starting MySQL.                                            [  OK  ]

  4、查看从服务器状态：

    mysql> show slave status\G;

    *************************** 1. row***************************

    Slave_IO_State: Waiting for master to sendevent

    Master_Host: 192.168.10.204

    Master_User: jack

    Master_Port: 3306

    Connect_Retry: 60

    Master_Log_File: master-log.000008

    Read_Master_Log_Pos: 1537

    Relay_Log_File: relay-bin.000004

    Relay_Log_Pos: 536

    Relay_Master_Log_File: master-log.000008

    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: 1537

    Relay_Log_Space: 827

    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: 10

    Master_SSL_Crl:

    Master_SSL_Crlpath:

    Using_Gtid: No

    Gtid_IO_Pos:

在主服务器创建数据库，查看是否可以同步到从服务器：

  主服务器： 

    mysql> create databases slave; #创建新的数据库

    mysql> show databases;  #查看是否创建完成

        +--------------------+

        | Database           |

        +--------------------+

        | hellodb            |

        | information_schema |

        | mysql              |

        | performance_schema |

        | s                  |

        | slave              |

        | test               |

        +--------------------+

    mysql> USE  slave; #切换的创建的数据库

    Database changed

    mysql> create table t1 (id int); #创建新的表

        Query OK, 0 rows affected (0.07 sec)

    mysql> INSERT INTO t1 VALUES (1),(2),(3); #向表插入简单数据

        Query OK, 3 rows affected (0.01 sec)

        Records: 3  Duplicates: 0  Warnings: 

    mysql> select * from t1; #验证数据是否成功写入

        +------+

        | id   |

        +------+

        |    1 |

        |    2 |

        |    3 |

        +------+

        3 rows in set (0.00 sec)

 从服务器验证：

    mysql> show databases; #查看是否有主服务器创建的数据库

        +--------------------+

        | Database           |

        +--------------------+

        | hellodb            |

        | information_schema |

        | mysql              |

        | performance_schema |

        | s                  |

        | slave              |

        | test               |

        +--------------------+

        7 rows in set (0.07 sec)

    mysql> use slave; #切换的主服务器的数据库

        Database changed

    mysql> select * from t1;  @查询是否有主服务器表的信息

        +------+

        | id   |

        +------+

        |    1 |

        |    2 |

        |    3 |

        +------+

        3 rows in set (0.00 sec)

二：主服务器运行中并且有一定的数据再加入从服务器：

    此过程要先把主服务器的数据备份出来，然后拷贝到从服务器上导入到从服务器的数据库，然后在让从服务器从主服务器的指定位置开始备份数据即可。

   主服务器导出数据：

root@node4 binlogs]# /usr/local/mysql/bin/mysqldump --all-databases --flush-logs --master-data=2 --lock-tables > /backup.sql

[root@node4 binlogs]# scp /backup.sql  192.168.10.205:/

    root@192.168.10.205's password: 

    backup.sql                               100%  518KB 518.0KB/s   00:00    

    从服务器导入数据：

    [root@node5 relaylogs]# mysql < /backup.sql

    mysql>CHANGE MASTER TOMASTER_HOST='192.168.10.204',MASTER_USER='jack',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-log.000009',MASTER_LOG_POS=367;  #指定同步主服务器的用户、密码、日志文件和开始的编号

    QueryOK, 0 rows affected (0.07 sec)

    mysql>START SLAVE;  #启动同步进程

    QueryOK, 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.10.204

    Master_User:jack

    Master_Port:3306

    Connect_Retry:60

    Master_Log_File:master-log.000009

    Read_Master_Log_Pos:367

    Relay_Log_File:relay-bin.000002

    Relay_Log_Pos:536

    Relay_Master_Log_File:master-log.000009

    Slave_IO_Running:Yes

    Slave_SQL_Running:Yes #看到这两个显示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:367

    Relay_Log_Space:827

    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:10

    Master_SSL_Crl:

    Master_SSL_Crlpath:

    Using_Gtid:No

    Gtid_IO_Pos:

    1 row inset (0.00 sec)

    从服务器验证数据库是否同步成功：

    mysql> show databases;

        +--------------------+

        | Database           |

        +--------------------+

        | hellodb            |

        | information_schema |

        | mysql              |

        | performance_schema |

        | s                  |

        | slave              |

        | test               |

        +--------------------+

        7 rows in set (0.00 sec)

    mysql> use slave;

        Database changed

    mysql> show tables;

        +-----------------+

        | Tables_in_slave |

        +-----------------+

        | t1              |

        +-----------------+

        1 row in set (0.00 sec)

    mysql> select * from t1;  #已经成功同步到主服务器的数据

        +------+

        | id   |

        +------+

        |    1 |

        |    2 |

        |    3 |

        +------+

        3 rows in set (0.00 sec)

   在主服务器新建一个数据库并向之前的t1表中插入数据查看是否可以同步成功： 

   主服务器：

    mysql> create database Slave2;

        Query OK, 1 row affected (0.00 sec)

    mysql> INSERT INTO t1 VALUES (4),(5),(6);

        Query OK, 3 rows affected (0.00 sec)

        Records: 3  Duplicates: 0  Warnings: 0

    从服务器验证： 

    mysql> show databases;

        +--------------------+

        | Database           |

        +--------------------+

        | Slave2             |

        | hellodb            |

        | information_schema |

        | mysql              |

        | performance_schema |

        | s                  |

        | slave              |

        | test               |

        +--------------------+

        8 rows in set (0.00 sec)

    mysql> use slave;

        Database changed

    mysql> show tables;

        +-----------------+

        | Tables_in_slave |

        +-----------------+

        | t1              |

        +-----------------+

        1 row in set (0.00 sec)

    mysql> select * from t1;

        +------+

        | id   |

        +------+

        |    1 |

        |    2 |

        |    3 |

        |    4 |

        |    5 |

        |    6 |

        +------+

        9 rows in set (0.00 sec)

    至此，从服务器已经从主服务器完成同步之前的数据，并可以同步新的数据

注：操作过程当中遇到两个问题，如下：

1、主服务器的数据库不能备份，执行命令式报错：

[root@node4 binlogs]# mysqldump --all-databases --flush-logs --master-data=2 --lock-tables > /Backup.sql

mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': Unknown system variable 'OPTION' (1193)

    问题分析:改错误是在调用 mysqldump命令是使用的默认路径/usr/bin/mysqldump，但是由于MariaDB是安装的目前的最新版本，此备份工具已经比较旧了，有些特性不支持，因此可以使用MariaDB解压包里面的备份工具即可。

2、从服务器一直不能与主服务器同步

具体表现为从服务器一直显示IO进程连接正在连接：

mysql>show slave status\G;

***************************1. row ***************************

Slave_IO_State:Connecting to master

Master_Host:192.168.10.204

Master_User:jack

Master_Port:3306

Connect_Retry:60

Master_Log_File:

Read_Master_Log_Pos:4

Relay_Log_File:relay-bin.000001

Relay_Log_Pos:4

Relay_Master_Log_File:

Slave_IO_Running:Connecting

Slave_SQL_Running:Yes

查看日志先显示账号访问主服务器失败，那么就判断为要么没有权限要么密码不对，在确认权限正确的情况下更改密码，然后将从服务器的slave停止，使用新密码重新启动slav即可，如下；

    50119  1:38:25 [ERROR] Slave I/O: error connecting to master 'jack@192.168.10.204:3306' - retry-time: 60  retries: 86400  message: Access denied for user 'jack'@'node5.a.com' (using password: YES), Internal MariaDB error code: 1045