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/mysql socket=/var/lib/mysql/mysql.sock `# 新添加的部分 # 配置主从时需要添加以下信息 start innodb_file_per_table=no log-bin=/var/lib/mysql/master-bin #log-bin没指定存储目录，则是默认datadir指向的目录 binlog_format=mixed server-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.log pid-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/mysql socket=/var/lib/mysql/mysql.sock # disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 `#配置主从时需要添加以下信息 start innodb_file_per_table=no server-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.log pid-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 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: 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: 200
1 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 names
you can turn off this feature to get a quicker startup with -a
database changed
mariadb [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 names you can turn off this feature to get a quicker startup with -a database changed mariadb [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: 0
1 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: 4 160915 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而不是mysql！
使用系统自带的repos安装很简单：
yum install mariadb mariadb-server
- systemctl start mariadb ==> 启动mariadb
- systemctl enable mariadb ==> 开机自启动
- mysql_secure_installation ==> 设置 root密码等相关
- mysql -u root -p 123456 ==> 测试登录！
结束！
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。