MariaDB设置主从复制

主从复制包含两个步骤: 在 master 主服务器(组)上的设置,以及在 slave 从属服务器(组)上的设置.

在网站设计、成都做网站过程中，需要针对客户的行业特点、产品特性、目标受众和市场情况进行定位分析，以确定网站的风格、色彩、版式、交互等方面的设计方向。成都创新互联公司还需要根据客户的需求进行功能模块的开发和设计，包括内容管理、前台展示、用户权限管理、数据统计和安全保护等功能。

配置主服务器 master

1、如果没有启用，则需要开启二进制日志.

给 master 设置唯一的  server_id ,所有的 slave 从属服务器也要设置  server_id; server_id值可以是整数型的数字(1 ~ 2^31-1), 在同一个复制组(replicating group)中的每台服务器的server_id都必须是唯一的.

[MySQLd]

server-id=1

log-bin=mysql-bin

binlog_format=mixed

2、创建一个复制账号，并授予replication slave权限。slave 从属服务器需要有连接并从master复制的权限. 通常是为每一台slave 创建一个单独的用户(user),并且只授予复制的权限(REPLICATION SLAVE 权限).

示例

GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'slave_host' IDENTIFIED BY 'bigs3cret';   

FLUSH PRIVILEGES;  

    MariaDB [(none)]> grant replication slave on *.* to 'repl_user'@'192.168.1.53' identified by 'pancou';

Query OK, 0 rows affected (0.00 sec)

        MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

需要注意，有一些系统配置选项可能会影响主从复制，查看下面的变量以避免发生问题:

skip-networking,如果 "skip-networking=1"，则服务器将限制只能由localhost连接，阻止其他机器远程连到此服务器上。

bind_address,类似地，如果 服务器只监听 127.0.0.1(localhost)的TCP/IP连接,则远程的 slave也不能连接到此服务器.

3、在主库上，设置读锁定有效，这个操作是为了确保没有数据库操作，以便获得一个一致性的快照：

MariaDB [(none)]> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

    4、然后得到主库上当前二进制文件名和偏移量值。这个操作的目的是在数据库启动以后，从这个点开始进行数据恢复。

       MariaDB [(none)]> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000002 |      509 |              |                  |

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

1 row in set (0.00 sec)

    5、现在主数据已经停止了更新操作，需要进行主数据库备份。 如果主数据库可以停止那么直接复制数据文件应该是最快的方法。

       [root@localhost ~]# mysqldump -uroot -p --quick --all-databases --lock-all-tables  --master-data=2 > /opt/data-all.sql

  Enter password: 

  [root@localhost ~]# ll -h /opt/data-all.sql 

       -rw-r--r-- 1 root root 3.7M May  2 15:08 /opt/data-all.sql

    6、主库备份完毕以后，可以恢复写操作，剩下的操作只需要在从库上操作。

        MariaDB [(none)]> unlock tables;

        Query OK, 0 rows affected (0.00 sec)

    7、将主数据库的一致性备份数据传送到从库上。

        [root@localhost ~]# rsync -avH --progress '-e ssh -p 22' /opt/data-all.sql root@192.168.1.53:/tmp/

The authenticity of host '192.168.1.53 (192.168.1.53)' can't be established.

RSA key fingerprint is 75:b3:14:47:e1:73:10:24:a8:8f:b8:05:29:3e:7d:30.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.1.53' (RSA) to the list of known hosts.

reverse mapping checking getaddrinfo for bogon [192.168.1.53] failed - POSSIBLE BREAK-IN ATTEMPT!

root@192.168.1.53's password: 

sending incremental file list

data-all.sql

    3863888 100%   23.88MB/s    0:00:00 (xfer#1, to-check=0/1)

sent 3864436 bytes  received 31 bytes  594533.38 bytes/sec

total size is 3863888  speedup is 1.00

配置从属服务器 slave

1、给 slave 指定唯一的 server_id. 所有服务器，不管是主服务器，还是从服务器,都要设置 server_id. server_id值可以是整数型的数字(1 ~ 2^31-1), 在同一个复制组(replicating group)中的每台(/个)服务器的server_id都必须是唯一的.

要让此配置项生效,需要重新启动服务.

  [mysqld]

server-id=2

2、在从库上恢复数据

        [root@localhost ~]# mysql

    3、在从库上使用--skip-salve-start选项启动从服务器，这样不会立即启动从数据库服务器上的复制进程，方便我们对数据库的服务进

       行进一步的设置。

       [root@www ~]# /usr/local/mysql/bin/mysqld_safe --skip-slave-start & 

    4、对从数据库做相应的设置，指定复制使用的用户，指定复制使用的用户，主数据库服务器的IP、端口以及开始执行复制的日志文件和

       位置等。

        CHANGE MASTER TO  

 MASTER_HOST='master.domain.com',  

 MASTER_USER='replication_user',  

 MASTER_PASSWORD='bigs3cret',  

 MASTER_PORT=3306,  

 MASTER_LOG_FILE='mariadb-bin.000096',  

 MASTER_LOG_POS=568,  

 MASTER_CONNECT_RETRY=10;  

       MariaDB [(none)]> change master to master_host='192.168.1.78',master_user='repl_user',master_password='pancou',master_log_file='mysql-bin.000002',master_log_pos=509;

Query OK, 0 rows affected (0.79 sec)

 5、在从库上，启动slave进程

  MariaDB [(none)]> start slave;

Query OK, 0 rows affected (0.00 sec)

6、这时在salve上，执行show processlist命令将显示类似如下进程：

  MariaDB [(none)]> show processlist\G

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

     Id: 5

   User: root

   Host: localhost

     db: NULL

Command: Query

   Time: 0

  State: init

   Info: show processlist

Progress: 0.000

*************************** 2. row ***************************

     Id: 8

   User: system user

   Host: 

     db: NULL

Command: Connect

   Time: 10

  State: Waiting for master to send event

   Info: NULL

Progress: 0.000

*************************** 3. row ***************************

     Id: 9

   User: system user

   Host: 

     db: NULL

Command: Connect

   Time: 4

  State: Slave has read all relay log; waiting for the slave I/O thread to update it

   Info: NULL

Progress: 0.000

3 rows in set (0.00 sec)

        这表明slave已经连接上master了，并开始接收日志。

        主库上的进程：

        MariaDB [(none)]> show processlist\G

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

     Id: 7

   User: root

   Host: localhost

     db: NULL

Command: Query

   Time: 0

  State: init

   Info: show processlist

Progress: 0.000

*************************** 2. row ***************************

     Id: 9

   User: repl_user

   Host: 192.168.1.53:57532

     db: NULL

Command: Binlog Dump

   Time: 183

  State: Master has sent all binlog to slave; waiting for binlog to be updated

   Info: NULL

Progress: 0.000

2 rows in set (0.00 sec)

    7、查看从库复制状态

       MariaDB [(none)]> show slave status\G

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

              Slave_IO_State: Waiting for master to send event

                 Master_Host: 192.168.1.78

                 Master_User: repl_user

                 Master_Port: 3306

               Connect_Retry: 60

             Master_Log_File: mysql-bin.000003

         Read_Master_Log_Pos: 479

              Relay_Log_File: localhost-relay-bin.000004

               Relay_Log_Pos: 767

       Relay_Master_Log_File: mysql-bin.000003

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

             Relay_Log_Space: 1112

             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

1 row in set (0.00 sec)

验证复制服务的正确性，在主数据库上执行一个更新操作，观察是否在从库上的是否同步。

MariaDB [(none)]> create database pancou;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use database pancou;

ERROR 1049 (42000): Unknown database 'database'

MariaDB [(none)]> use pancou;

Database changed

MariaDB [pancou]> create table rpel_table(id int(3));

Query OK, 0 rows affected (0.39 sec)

MariaDB [pancou]> insert rpel_table value(1),(2),(3),(4),(5);

Query OK, 5 rows affected (0.04 sec)

Records: 5  Duplicates: 0  Warnings: 0

MariaDB [pancou]> select * from rpel_table;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|    5 |

+------+

5 rows in set (0.00 sec)

    MariaDB [pancou]> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000003 |      913 |              |                  |

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

1 row in set (0.00 sec)

    查看从库：

    MariaDB [(none)]> select * from pancou.rpel_table;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|    5 |

+------+

5 rows in set (0.00 sec)

MariaDB [(none)]> show slave status\G

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

              Slave_IO_State: Waiting for master to send event

                 Master_Host: 192.168.1.78

                 Master_User: repl_user

                 Master_Port: 3306

               Connect_Retry: 60

             Master_Log_File: mysql-bin.000003

         Read_Master_Log_Pos: 913

              Relay_Log_File: localhost-relay-bin.000004

               Relay_Log_Pos: 1201

       Relay_Master_Log_File: mysql-bin.000003

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

             Relay_Log_Space: 1546

             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

1 row in set (0.00 sec)