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 statusG*************************** 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 root@192.168.1.111:/tmp 从库配置步骤：
1、修改从服务器my.cnf配置文件 log_bin = mysql server_id = 22、重启mysql服务器service mysqld restar3、导入主备份文件# 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 statusG语句获取的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 = 2Mdatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockvalidate_password=OFFserver-id=1user=mysqllog-bin=mysql-binlog-slave-updatesslave-skip-errors=allsync_binlog=1auto-increment-increment = 1auto-increment-offset = 1# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0relay_log=/var/lib/mysql/mysql-relay-binlog-error=/var/log/mysqld.logpid-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 = 2Mdatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockvalidate_password=OFFserver-id=2log-bin=mysql-binlog-slave-updatesslave-skip-errors=allsync_binlog=1auto_increment_increment=2auto_increment_offset=1# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0relay_log=/var/lib/mysql/mysql-relay-binlog-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

本文永久更新链接地址