MySQL master-slave Separation

Master-slave MySQL master-slave Database Configuration

1./etc/My. CNF master database configuration

# Generally, it already exists.
Server-id = 1
# Databases to be copied
BINLOG-do-DB = search
# Databases that do not require Replication
Replicate-ignore-DB = MySQL
Replicate-ignore-DB = test
Replicate-ignore-DB = information_schema
# The generated file for synchronous data, serial number from: mysql-bin.000001
Log-bin = mysql-bin

2. Restart MySQL, log on to MySQL, and set the permissions required for the slave Database: you can log on from any machine, and the account/password is slave1/passw0rd.
Grant replication slave, reload, super on *. * To 'slave1 '@' % 'identified by 'passw0rd ';

3. view the status

Mysql> show Master status;

+ ------------------ + ---------- + -------------- + ------------------ +

| File | position | binlog_do_db | binlog_ignore_db |

+ ------------------ + ---------- + -------------- + ------------------ +

| Mysql-bin.000005 | 7657 | search |

+ ------------------ + ---------- + -------------- + ------------------ +

1 row in SET (0.01 Sec)

Bytes ---------------------------------------------------------------------------------------------------

Slave Database Configuration

1./etc/My. CNF is configured from the database. because MySQL versions do not support parameters similar to "Master-host" since 5.1.7, when you use the configuration file to set master-slave synchronization, the following error occurs: mysqld: unknown variable 'master-host = 127.0.0.1'

# Set the server-ID in the configuration file to 2
Server-id = 2

(It may not work. You need to log on to the database and use show variables like 'server _ id'; to view it. Set
Global server_id = 2 ;)

# Name of the database to be synchronized
Replicate-do-DB = mydatabase

2. Restart MySQL, log on to MySQL, and set synchronization settings. syslog.000001 is the log file, and master_log_pos = 0 is the start position of log replication, that is, synchronizing data from the 0 position of syslog.000001, process log files and line numbers based on actual conditions

Mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.00 Sec)

Mysql> change master

-> Master_host = '2017. 103.8.14 ', master_user = 'slave1', master_password = 'passw0rd ',

-> Master_log_file = 'mysql-bin.000005 ', master_log_pos = 7657, master_port = 53306;

Query OK, 0 rows affected (0.10 Sec)

Mysql> Start slave;

Query OK, 0 rows affected (0.00 Sec)

Show slave status \ G; the command is easier to renew