centos7 mariadb主从复制配置搭建

centos7 mariadb主从复制配置搭建

花了小一天的时间，终于实现了centos7 mariadb主从复制配置搭建，下面记录一下过程

环境:

虚拟机:vm8; centos7 版本： 7.2.1511 ; mariadb 版本：centos7 .2 内置的主库服务器： 10.69.5.200 ，CentOS 7 ，MariaDB 10 已安装，有数据。从库服务器 1 ： 10.69.5.201 ，CentOS 7 ，MariaDB 10 已安装，无应用数据。

主服务器配置

以下操作在主服务器192.168.71.151的/etc/my.cnf上进行。

1.修改配置文件，命令：vim /etc/my.cnf，输入下列代码：

[root@localhost ~] # cat /etc/my.cnf [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock` # 新添加的部分 # 配置主从时需要添加以下信息 start innodb_file_per_table=NOlog-bin=/var/lib/mysql/master-bin #log-bin没指定存储目录，则是默认datadir指向的目录 binlog_format=mixedserver-id= 200 #每个服务器都需要添加server_id配置，各个服务器的server_id需要保证唯一性，实践中通常设置为服务器IP地址的最后一位 #配置主从时需要添加以下信息 end ` # 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 [mysqld_safe]log- error =/var/log/mariadb/mariadb.logpid- file =/var/run/mariadb/mariadb.pid # # include all files from the config directory # !includedir /etc/ my .cnf.d

最后，:wq!保存退出

2.重启mariadb服务，输入命令 [root@localhost ~]# systemctl restart mariadb.service

3.登录mariadb [root@localhost ~]# mysql -u root -padmin 注：-p后是密码，中间没有空格 4.创建帐号并赋予replication的权限 从库,从主库复制数据时需要使用这个帐号进行

MariaDB [( none )]> GRANT REPLICATION SLAVE ON *.* TO 'root' @ '10.69.5.%' IDENTIFIED BY 'admin' ; Query OK, 0 rows affected ( 0 . 00 sec)

5.备份数据库数据，用于导入到从数据库中

加锁

实际工作中，备份的时候是不让往库中写数据的，所以数据库要加锁，只能读

MariaDB [( none )]> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected ( 0 . 00 sec)

记录主库log文件及其当前位置

MariaDB [(none)]> SHOW MASTER STATUS;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 | 694 | | |+------------------+----------+--------------+------------------+

记住File和Position的部分，从服务器会用到

备份数据，输入命令：

[root@localhost ~] # mysqldump -uroot -p --all-databases > /root/db.sql

解锁 主库

数据备份完成后，就可以释放主库上的锁：

MariaDB [( none )]> UNLOCK TABLES; Query OK, 0 rows affected ( 0 . 00 sec)

从服务器配置

以下在从服务器上的操作

1.导入主库的数据

[root@localhost ~] # mysql -uroot -p < db.sql

2.从服务器/etc/my.cnf配置,设置relay-log my.cnf文件中添加一行relay_log=relay-bin 如果不设置，默认是按主机名 + “-relay-bin”生成relay log。

[root@localhost ~] # cat /etc/my.cnf [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links= 0 ` #配置主从时需要添加以下信息 start innodb_file_per_table=NOserver-id= 201 #一般与服务器ip的最后数字一致 relay-log=/var/lib/mysql/relay-bin #配置主从时需要添加以下信息 end ` # 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 [mysqld_safe]log- error =/var/log/mariadb/mariadb.logpid- file =/var/run/mariadb/mariadb.pid # # include all files from the config directory # !includedir /etc/ my .cnf.d

3.重启服务

[root@localhost ~] # systemctl restart mariadb.service

4.登录mariadb

[root@localhost ~] # mysql -u root -padmin

5.设置主从复制

MariaDB [( none )]> CHANGE MASTER TO MASTER_HOST= '10.69.5.200' ,MASTER_USER= 'root' , MASTER_PASSWORD= 'admin' , MASTER_LOG_FILE= 'master-bin.000001' , MASTER_LOG_POS= 694 ; Query OK, 0 rows affected ( 0 . 02 sec)

这个命令完成以下几个任务：

a.设置当前服务器为主服务器(10.69.5.200)的从库b.提供当前数据库(从库)从主库复制数据时所需的用户名和密码，即上面的 GRANT REPLICATION SLAVE ON *.* TO 'root' @ '10.69.5.%' IDENTIFIED BY 'admin' ;设置的c.指定从库开始复制主库时需要使用的日志文件和文件位置，即上面主库执行 SHOW MASTER STATUS ;显示结果中的File和Position

6.开启主从复制

MariaDB [( none )]> START SLAVE; Query OK, 0 rows affected ( 0 . 00 sec)

7.查看从库状态

MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.69.5.200 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 694 Relay_Log_File: relay-bin.000003 Relay_Log_Pos: 530 Relay_Master_Log_File: master-bin.000001 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: 694 Relay_Log_Space: 818 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: 0Master_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: 2001 row in set (0.00 sec)

注意：结果中 Slave_IO_Running 和 Slave_SQL_Running 必须为 Yes ，如果不是，需要根据提示的错误修改。

验证

主服务器：

MariaDB [(none)]> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || mytest || performance_schema || test |+--------------------+5 rows in set (0.04 sec)MariaDB [(none)]> use mytest;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [mytest]> select * from user;+----+------+| id | name |+----+------+| 1 | t || 2 | t2 || 3 | t3 |+----+------+3 rows in set (0.00 sec)MariaDB [mytest]> insert into user(name) values('t4');Query OK, 1 row affected (0.01 sec)MariaDB [mytest]> select * from user;+----+------+| id | name |+----+------+| 1 | t || 2 | t2 || 3 | t3 || 4 | t4 |+----+------+4 rows in set (0.00 sec)

查看从服务器数据是否变化：

MariaDB [(none)]> use mytest;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [mytest]> select * from user;+----+------+| id | name |+----+------+| 1 | t || 2 | t2 |+----+------+2 rows in set (0.00 sec)MariaDB [mytest]> select * from user;+----+------+| id | name |+----+------+| 1 | t || 2 | t2 || 4 | t4 |+----+------+3 rows in set (0.00 sec)

可以看到，从服务器更新了数据

搭建过程中遇到的问题及解决方法

问题1：从服务器设置主从复制出现错误:

MariaDB [mytest]> start slave;ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MariaDB error log发现 Slave_IO_Running: No Slave_SQL_Running: No 进一步发现我输入的是：CHANGE MASTER TO MASTER_HOST='192.168.71.151',MASTER_USER='slave_user', MASTER_PASSWORD='bigs3cret', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 469; 重新输入：MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.69.5.200',MASTER_USER='root', MASTER_PASSWORD='admin', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 469;报错：ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MariaDB error log于是看错误日志：/var/log/mariadb/mariadb.log错误日志的位置在/etc/my.cnf中配置：log-error=/[root @localhost ~]# cat /var/log/mariadb/mariadb.log 160915 12:52:02 [ERROR] Failed to open the relay log './mariadb-relay-bin.000001' (relay_log_pos 4)160915 12:52:02 [ERROR] Could not find target log during relay log initialization通过查找答案： 删除/var/lib/mysql/路径下the ‘master.info’ ‘mysqld-relay-bin. * ’ ‘relay-log.info’ ‘relay-log-index. * ’ 运行命令：rm -rf master.info,rm -rf * relay * 重启服务：[root @localhost mysql]# systemctl restart mariadb.service 进入mariadb:[root @localhost mysql]# mysql -u root -padmin MariaDB [(none)]> flush logs;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> reset slave;Query OK, 0 rows affected (0.00 sec)重新设置主从复制关系：MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.69.5.200',MASTER_USER='root', MASTER_PASSWORD='admin', MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS= 694;Query OK, 0 rows affected (0.02 sec)这次成功了。MariaDB [(none)]> start slave;Query OK, 0 rows affected (0.01 sec)查看从库状态：MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 10.69.5.200 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 694 Relay_Log_File: relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Connecting Slave_SQL_Running: Yes ··· ··· ··· Replicate_Ignore_Server_Ids: Master_Server_Id: 01 row in set (0.00 sec)发现问题2.Slave_IO_Running: Connecting

问题2.Slave_IO_Running: Connecting 查看错误日志

[root@localhost ~]# cat / var / log /mariadb/mariadb.log···160915 13:17:56 [ Note ] Slave SQL thread initialized, starting replication in log 'master-bin.000001' at position 694, relay log '/ var /lib/mysql/relay-bin.000001' position: 4160915 13:17:56 [ ERROR ] Slave I/O: error connecting to master 'root@10.69.5.200:3306' - retry-time: 60 retries: 86400 message: Can't connect to MySQL server on '10.69.5.200' (113), Error_code: 2003

这时运行telnet命令

[root@localhost ~] # telnet 10.69.5.200 3306 -bash: telnet: 未找到命令

安装telnet

[root@localhost ~]# yum -y install telnet-server .x86_64安装成功后重启 telnet 服务[root@localhost ~]# systemctl start telnet .socket[root@localhost ~]# systemctl enable telnet .socket[root@localhost ~]# telnet 10.69.5.200 3306 -bash : telnet : 未找到命令还是不行

这回我reboot重启虚拟机，运行命令

注意：这回不是 "yum -y install telnet-server.x86_64" 了，这回没有telnet-server了[root@localhost ~] # yum install telnet.x86_64 运行成功了接着[root@localhost ~] # systemctl enable telnet.socket [root@localhost ~] # systemctl start telnet.socket [root@localhost ~] # firewall-cmd --add-service=telnet --permanent success[root@localhost ~] # telnet telnet>telnet终于安装成功了

搭建centos7 mariadb主从复制配置参考资料 http://ourmysql.com/archives/1370 http://joelhy.github.io/2015/02/06/mariadb-master-slave-replication/ http://www.voidcn.com/blog/c571121319/article/p-1554892.html

解决问题参考资料： 问题1： http://www.slickdev.com/2008/10/20/mysql-slave-failed-to-open-the-relay-log/ http://bbs.chinaunix.net/thread-1349827-1-1.html http://dev.nuclearrooster.com/2009/03/06/relay-log-issues-when-starting-mysql-replication-slave/  问题2： yum install telnet-server systemctl enable telnet.socket systemctl start telnet.socket firewall-cmd --add-service=telnet --permanent

http://www.cnblogs.com/hellotracy/articles/5143960.html

参考：http://joelhy.github.io/2015/02/06/mariadb-master-slave-replication/http://www.voidcn.com/blog/c571121319/article/p-1554892.htmlhttp://www.cnblogs.com/hellotracy/articles/5143960.html