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)
