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