一.MySql复制环境:
原理: MySql复制是指将主数据库的ddl和dml语言操作通过二进制日志传到复制服务器上,然后在复制服务器上将这些日志文件重做,从而使得从服务器和主服务器的数据保持同步!
目的:方便不停止数据库而备份主服务器,实现主从服务器数据同步!
现有两台机器A、B需要实施MySql的主从热备份,A作为主服务器,B作为从服务器
环境:Centos 7.4、MySql5.7
A、B的操作系统一样,MySql版本均为5.7
A主服务器ip:192.168.56.103
B从服务器ip:192.168.56.104
二、MySql复制的优点:
- 如果主服务器上出现了问题可以切换到从服务器上
- 可以在从服务器上执行查询操作,降低主服务器的访问压力
- 可以在从服务器上进行备份,以避免备份期间影响主服务器的服务
三、Master配置
在Linux环境下MySQL的配置文件的位置是在 /etc/my.cnf ,在该文件下指定Master的配置如下:
[root@localhost ~]# vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server_id = 1
expire_logs_days = 3
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
binlog-format=ROW
log-bin=mysql-bin
log-slave-updates=true
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=personalsite
binlog-ignore-db=test
binlog-do-db=pratise
-
server-id用于标识唯一的数据库,这里设置为1,在设置从库的时候就需要设置为其他值。
-
binlog-ignore-db:表示同步的时候ignore的数据库
-
binlog-do-db:指定需要同步的数据库
1、然后重启mysql:service mysqld restart
2、进入mysql:[root@VM_221_4_centos ~]# mysql -u root -p 回车,输入mysql密码进入。
3、 赋予从库权限帐号,允许用户在主库上读取日志,赋予192.168.56.104也就是Slave机器有File权限,只赋予Slave机器有File权限还不行,还要给它REPLICATION SLAVE的权限才可以。
mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO 'slave'@'192.168.56.104' IDENTIFIED BY '123456';
mysql> FLUSH PRIVILEGES
#查看主节点状态
mysql> show master status
#在主库上查看已连接的slave主机
mysql> show slave hosts;
#查看所有binlog日志
mysql> show binary logs;
#查看所有binlog 事件
mysql> show binlog events in 'mysql-bin.000003';
这里的 File 、Position 是在配置Salve的时候要使用到的,Binlog_Do_DB表示要同步的数据库,Binlog_Ignore_DB 表示Ignore的数据库,这些都是在配置的时候进行指定的。
记录这两个参数
File: mysql-bin.000003
Position: 458
四、Slave配置
在Linux环境下MySQL的配置文件的位置是在 /etc/my.cnf ,在该文件下指定Slave的配置如下:
[root@localhost ~]# vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server_id = 2
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id = 2
log-bin=mysql-slave-bin
binlog-do-db=pratise
# 需要同步的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=personalsite
binlog-ignore-db=test
# skip-grant-tables
-
在master端不指定binlog-do-db,在slave端用replication-do-db来过滤
-
replicate-ignore-db = mysql #忽略的库
-
server-id用于标识唯一的数据库,这里设置为2,在设置从库的时候就需要设置为其他值。
-
binlog-ignore-db:表示同步的时候ignore的数据库
-
binlog-do-db:指定需要同步的数据库
1、然后重启mysql:service mysqld restart
2、进入mysql:[root@VM_221_4_centos ~]# mysql -u root -p 回车,输入mysql密码进入。
3、 配置连接主服务器的信息
mysql> stop slave;
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.56.103',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;
mysql> start slave;
#查看从服务器状态
mysql> show slave status \G;
- Slave_IO_Running=Yes
- Slave_SQL_Running=Yes
则表示配置成功
手动同步
change master to
master_host='192.168.56.102',
master_user='slave',
master_password='123456',
master_port=3306,
master_log_file='localhost-bin.000001',
master_log_pos=426 ;