MySQL 5.7 Master Backup configuration detailed

MySQL 5.7 Primary Backup configuration

1. Main configuration steps

Main Library configuration steps:

1, Grant created the user and authorized, IP for the IP from the server, the meaning of this sentence is to create a user named uname, password upwd users, this user can only access from the 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, modify the MY.CNF configuration file as follows:

Log-bin=mysql-bin #启动二进制文件 2 server_id=1
#服务器ID
3, restart MySQL

At this point you can view the primary server binlog log position value

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. Lock All Tables

Mysql> FLUSH TABLES with READ LOCK; 

5. Backup table

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

6, Unlock

Mysql> UNLOCK TABLES; 

7, upload the data from the library (192.168.3.115)

# SCP All_db.sql root@192.168.1.111:/tmp

To configure from the library step: 1, modify the MY.CNF configuration file from the server

Log_bin = MySQL
server_id = 2
2, restart the MySQL server

Service Mysqld Restar
3, import the main backup file

# mysql-uroot-p </tmp/all_db.sql
4, Synchronous Binlog log

mysql> Reset Slave;
Query OK, 0 rows Affected (0.00 sec)
Note: master_user= ' repl_user ', master_password= ' zcxc123 ' is set by the first step of the main Library grant replication statement
Master_log_file= ' mysql-bin.000080 ', master_log_pos=154 is the main library third step show Master STATUS\G statement gets
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)
The main configuration is to follow the above steps to configure the above from the library to the main library. You do not need to back up the table when you configure it.

2. Configuration file

Configuration file 1

-bash-4.1# more/etc/my.cnf
# for advice on I-Change settings
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove Leading # and set to the amount ' RAM for ' most important data
# cache in MySQL. Start at 70% all RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove Leading # to turn on a very important data integrity Option:logg ING
# Changes to the binary log between backups.
# Log_bin
#
# Remove Leading # to set options mainly USEF UL 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#
Configuration file 2-bash-4.1# more/etc/my.cnf

# for advice on I-Change settings
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[Mysqld]
#
# Remove Leading # and set to the amount's RAM for the most important data
# Cache in MySQL. Start at 70% all 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