前置条件:
虚拟机:vm8; centos7 版本:7.2.1511; mariadb 版本:centos7.2内置的
主库服务器: 192.168.0.158,CentOS 7,MariaDB
从库服务器1: 192.168.0.155,CentOS 7,MariaDB
mariadb安装请参考:
yum install mariadb mariadb-server
systemctl start mariadb mariadb.service
mysql_secure_installation
#此处直接按回车即可(一开始没有密码,以后改密码则需要输入已设定的密码)
然后会要求你输入两次密码,然后回车
然后一路按Y加回车
直到出现Thanks for using MariaDB!
主服务器配置
以下操作在主服务器192.168.0.158的/etc/my.cnf上进行
1.修改配置文件,命令:vim /etc/my.cnf,输入下列代码:
[root@localhost ~]# vim /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=158
#每个服务器都需要添加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 -p123456 #记住P后面是密码,中间没有空格
4.创建帐号并赋予replication的权限
从库,从主库复制数据时需要使用这个帐号进行
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'test'@'%' IDENTIFIED BY '123456';
#尽量用test,不用root,%号代表所以ip都可以访问,123456为密码
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 |
+-------------------+----------+--------------+------------------+
| master-bin.000001 | 245 | | |
记住File和Position的部分,从服务器会用到
备份数据,输入命令:
[root@localhost ~]# mysqldump -uroot -p123456 --all-databases > /root/db.sql
解锁 主库
数据备份完成后,就可以释放主库上的锁:
MariaDB [(none)]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
从服务器配置
以下在从服务器上的操作
1.导入主库的数据
[root@localhost ~]# mysql -uroot -p123456 < db.sql
其实我发现这个命令不管用,还有一种方法就是点击下图标记的地方
进入下图界面,把db.sql文件复制到左边本地后,再从本地复制给从库。
2.从服务器/etc/my.cnf配置,设置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=155 #一般与服务器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='192.168.0.158',MASTER_USER='root', MASTER_PASSWORD='123456', MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS= 245;
Query OK, 0 rows affected (0.02 sec)
这个命令完成以下几个任务:
a.设置当前服务器为主服务器(192.168.0.158)的从库
b.提供当前数据库(从库)从主库复制数据时所需的用户名和密码
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: 192.168.0.158
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 387
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 672
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: 387
Relay_Log_Space: 960
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: 158
1 row in set (0.00 sec)
注意:结果中Slave_IO_Running和Slave_SQL_Running必须为Yes,如果不是,需要根据提示的错误修改。
**搭建过程中遇到的问题及解决方法
**发现问题1:Slave_IO_Running: Connecting
这时运行telnet命令
[root@localhost ~]# telnet 10.69.5.200 3306
[root@localhost ~]# yum -y install telnet-server.x86_64
[root@localhost ~]# systemctl start telnet.socket
[root@localhost ~]# systemctl enable telnet.socket
[root@localhost ~]# telnet 192.168.0.158 3306
# 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>
发现做了这么多,查看还是Slave_IO_Running: Connecting。怎么办呢?在大佬的帮助下搞定了。
创建帐号并赋予replication的权限
从库,从主库复制数据时需要使用这个帐号进行
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'test'@'%' IDENTIFIED BY '123456';
#我之前用的是root.现在改成test.
Query OK, 0 rows affected (0.00 sec)
然后要么就给主库添加端口3306,要么就把防火墙给关了
systemctl stop firewalld #关闭防火墙
再次去从库查看就可以了。
MariaDB [(none)]> stop SLAVE;
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> show slave status\G
验证一下:
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
#新建一个mytest数据库。
MariaDB [(none)]> create database mytest;
Query OK, 1 row affected (0.00 sec)
查看从服务器数据是否变化:
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mytest |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
可以看到,从服务器更新了数据。mariadb 主从复制就搭建成功啦。