MySQL 5.7主主備份配置詳解

下面我們來看一篇關於MySQL 5.7主主備份配置詳解,希望這篇文章能夠幫助到各位朋友,具體的細節步驟如下文介紹.

MySQL 5.7主主備份配置

1. 主要配置步驟

主庫配置步驟：

1、GRANT創建用戶並授權，ip為從服務器的ip,本句含義是為創建一個用戶名為uname，密碼為upwd的用戶，這個用戶只能從192.168.1.111上進行訪問

mysql> grant replication slave on *.* to 'repl_user'@'192.168.3.115' identified by 'zcxc123';
2 Query OK, 0 rows affected (0.01 sec)
2、修改my.cnf配置文件如下:

  log-bin=mysql-bin  #啟動二進制文件  2 server_id=1 
    #服務器ID 
3、重啟mysql

此時可以查看主服務器binlog日志position值 

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000080
         Position: 154
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
4、鎖定所有表

mysql>  FLUSH  TABLES  WITH  READ  LOCK; 

5、備份表

[root@localhost  mysql]#  mysqldump  -uroot  -p  --all-databases  -l  -F  >all_db.sql 

6、解鎖

mysql>  UNLOCK  TABLES; 

7、把數據傳到從庫(192.168.3.115)

#  scp  all_db.sql  [email protected]:/tmp 

從庫配置步驟：1、修改從服務器my.cnf配置文件  

 log_bin  =  mysql 
 server_id  =  2
2、重啟mysql服務器

service  mysqld  restar
3、導入主備份文件

#  mysql  -uroot  -p  </tmp/all_db.sql
4、同步binlog日志

mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
注：master_user='repl_user',master_password='zcxc123' 是主庫第一步 grant replication 語句設置的
   master_log_file='mysql-bin.000080',master_log_pos=154  是主庫第三步show master status\G語句獲取的
mysql> change master to master_host='192.168.3.116',master_user='repl_user',master_password='zcxc123',master_log_file='mysql-bin.000080',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
主主配置就是，按照以上步驟，把上面從庫按主庫配置一遍。再配置時 不用備份表了。

2. 配置文件

配置文件1

-bash-4.1# more /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
validate_password=OFF
server-id=1
user=mysql
log-bin=mysql-bin
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto-increment-increment = 1
auto-increment-offset = 1

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
relay_log=/var/lib/mysql/mysql-relay-bin
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
-bash-4.1#
配置文件2-bash-4.1# more /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
validate_password=OFF
server-id=2
log-bin=mysql-bin
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

relay_log=/var/lib/mysql/mysql-relay-bin
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid