[Linux服务器使用] Mysql 主从复制

一.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复制的优点：

1. 如果主服务器上出现了问题可以切换到从服务器上
2. 可以在从服务器上执行查询操作，降低主服务器的访问压力
3. 可以在从服务器上进行备份，以避免备份期间影响主服务器的服务

三、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 ;