CNDBA社区
1 测试环境说明
这里的测试假设之前的主库已经在运行，有生产数据。 然后在同步到新的环境。 测试的主从机器都是redhat 6.7.
[root@www.cndba.cn/dave ~]# lsb_release -a
LSB Version: :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
Distributor ID: RedHatEnterpriseServer
Description: Red Hat Enterprise Linux Server release 6.7 (Santiago)
Release: 6.7
Codename: Santiago
[root@www.cndba.cn/dave ~]#
mysql> select version();
+---------------------+
| version() |
+---------------------+
| 10.2.12-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)
MariaDB的安装这里不再描述，如有不清楚的参考：
Linux 平台 MariaDB 10.2 安装手册
http://www.cndba.cn/dave/article/2630
确保主从机器的防火墙都已经关闭：
[root@MariaDB /]# chkconfig iptables off
[root@MariaDB /]# service iptables stop
iptables: Setting chains to policy ACCEPT: filter [ OK ]
iptables: Flushing firewall rules: [ OK ]
iptables: Unloading modules: [ OK ]
[root@MariaDB /]#
2 主库配置
2.1 修改配置文件：
在/etc/my.cnf文件中，加上
server-id=1
log-bin= /mysql/binlog/mysql-bin
2.2 在主数据库建立账户并授权从数据库访问：
grant replication slave on *.* to 'slave'@'%' identified by 'slave';
2.3 重启数据库
[root@www.cndba.cn/dave binlog]# service mysql restart
Shutting down MySQL.. [ OK ]
Starting MySQL.180209 06:36:05 mysqld_safe Logging to '/mysql/log/www.cndba.cn-error.log'.
180209 06:36:05 mysqld_safe Starting mysqld daemon with databases from /mysql/data
[ OK ]
[root@www.cndba.cn/dave binlog]#
2.4 检查主数据库的master状态:
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 328 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
3 备份主库并恢复到从库
这里逻辑备份和物理备份都行，只要把数据同步到备库即可，关于他们的详细说明，参考：
MariaDB 的逻辑备份与恢复
http://www.cndba.cn/dave/article/2650
MariaDB 使用 xtrabackup 工具进行备份与恢复
http://www.cndba.cn/dave/article/2652
因为xtrabackup需要单独的安装，所以我们这里直接使用msyqldump进行逻辑备份与恢复。
[root@www.cndba.cn/dave binlog]# mysqldump -u root -p --flush-logs --master-data=2 --single-transaction --routines --triggers --events --all-databases > /tmp/slave.sql
Enter password:
[root@www.cndba.cn/dave binlog]#
[root@www.cndba.cn/dave binlog]# scp /tmp/slave.sql 192.168.56.3:/tmp/
root@192.168.56.3's password:
slave.sql 100% 712KB 711.6KB/s 00:00
[root@www.cndba.cn/dave binlog]#
恢复到从库：
[root@MariaDB ~]# mysql -u root -p < /tmp/slave.sql
Enter password:
[root@MariaDB ~]#
MariaDB [mysql]> show databases;
+--------------------+
| Database |
+--------------------+
| cndba |
| information_schema |
| mysql |
| performance_schema |
| world |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [mysql]>
4 从库配置
4.1 修改配置文件：
在/etc/my.cnf文件中，加上
server-id=2
log-bin= /mysql/binlog/mysql-bin
relay-log = /mysql/relaybin/relay-bin
注：
log-bin是二进制文件
relay-log 是接收主库二进制的辅助文件。
4.2 重启从数据库
[root@MariaDB mysql]# service mysql restart
Shutting down MySQL.. [ OK ]
Starting MySQL.180210 04:14:41 mysqld_safe Logging to '/mysql/log/www.cndba.cn-error.log'.
180210 04:14:41 mysqld_safe Starting mysqld daemon with databases from /mysql/data
[ OK ]
[root@MariaDB mysql]#
4.3 在命令行配置从库：
如果使用xtrabackup工具进行备份，那么在xtrabackup_checkpoints 文件里会记录二进制文件和对应的位置。 我们这里使用的是mysqldump，所以直接查看备份的文件就可以了：
[root@MariaDB mysql]# cat /tmp/slave.sql |more
-- MySQL dump 10.16 Distrib 10.2.12-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 10.2.12-MariaDB-log
……
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=385;
执行：
MariaDB [(none)]> change master to master_host='192.168.56.2',master_user='slave',master_password='slave',master_log_file='mysql-bin.000004',master_log_pos=385;
Query OK, 0 rows affected (0.03 sec)
如果配置错误先停止slave，修改，在启动：
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> change master to master_host='192.168.56.2',master_user='slave',master_password='slave',master_log_file='mysql-bin.000004',master_log_pos=385;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
4.4 在从库启用复制功能
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
备注：
MariaDB在启动时, 默认也会自动启动复制(slave)。IO进程和SQL进程状态都为Yes. 如果想禁止启用此功能，可以在/etc/my.cnf配置文件的mysqld下添加一行：skip-slave-start，然后重启从库生效。
4.5 查看从数据库的复制功能状态
注意：结果中Slave_IO_Running和Slave_SQL_Running必须为Yes，如果不是，需要根据提示的错误修改。
MariaDB [(none)]> show slave status/G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.2
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 782
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 952
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: 782
Relay_Log_Space: 1255
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)
ERROR: No query specified
5 验证同步
主库：
MariaDB [mysql]> use cndba
Database changed
MariaDB [cndba]> show tables;
+-----------------+
| Tables_in_cndba |
+-----------------+
| dave |
| dave2 |
| oracle |
| zhixin |
+-----------------+
4 rows in set (0.00 sec)
MariaDB [cndba]> select * from dave;
+------+-------------------+
| id | name |
+------+-------------------+
| 1 | www.cndba.cn/dave |
+------+-------------------+
1 row in set (0.00 sec)
MariaDB [cndba]> insert into dave values(2,'www.zhixintech.cc');
Query OK, 1 row affected (0.00 sec)
MariaDB [cndba]> commit;
Query OK, 0 rows affected (0.00 sec)
备库查询
MariaDB [(none)]> use cndba
Database changed
MariaDB [cndba]> select * from dave;
+------+-------------------+
| id | name |
+------+-------------------+
| 1 | www.cndba.cn/dave |
| 2 | www.zhixintech.cc |
+------+-------------------+
2 rows in set (0.00 sec)
同步正常。
版权声明：本文为博主原创文章，未经博主允许不得转载。
