MySQL master-slave copy Notes

A: Test environment introduction

The master-slave replication test environment is Ubuntu+mysql5.7,master server IP is 192.168.71.135,slave server IP is a 192.168.71.137,ubuntu environment that is cloned from a server that has a configured environment.

II: MySQL master-slave configuration

1:) master configuration

A in the MySQL directory (/etc/mysql), add Server-id and Log-bin to the mysql.cnf file. The Server-id of the master-slave server cannot be the same

Log-bin=mysql-bin

server-id=100

When set, use Show master status;

Set up the account, password, etc. connected from the server

Configure Connected Users

GRANT REPLICATION SLAVE on * * to ' MySQL account ' @ '% ' identified by ' password '; Here is the test, no IP specified from the server. To specify IP, execute one of the following instructions

GRANT REPLICATION SLAVE on * * to ' MySQL account ' @ ' 192.168.71.137 ' identified by ' password ';

After configuration, log restart MySQL.

2:) Configure from Database

2.1 Slavethe configuration andMastersimilar to (Profile name: MYSQL.CNF, in the/etc/mysql directory, your environment may not be the directory), you will still need to restart after the configuration is completeslaveof theMySQL. The configuration parameters are as follows:
log_bin= Mysql-bin
Server_id= 110
Relay_log=mysql-relay-bin
Log_slave_updates=1
Read_only=1
server_idis a must, and the only. slaveThere is no need to turn on the binary log, but in some cases it must be set, for example, ifslavefor otherslaveof theMaster, you must setBin_log. Here we turn on the binary log, and the display of the named(The default name ishostname, however, ifhostnamethe change will cause problems.).
Relay_logConfigure the relay log,log_slave_updatesrepresentsslavewrite replication events into your binary log(you'll see how it's useful later.).
Some people turn onSlaveBinary log, but not setlog_slave_updates, and then viewslavethe data is not changed, this is a wrong configuration. So try to useread_only, which prevents data changes(In addition to special threads). However,read_onlyand is very practical, especially those that need to be inslaveCreate a table on the app.

2.2 Restart MySQL after configuration is complete

/etc/init.d/mysql restart

2.3 executing a synchronous SQL statement

Mysql> Change Master to
Master_host= ' 192.168.71.135 ',
Master_user= ' Syncuser ',
Master_password= ' 123456 ',
Master_log_file= ' mysql-bin.000001 ',--this value originates from the main library and executes the file field after show Master status
master_log_pos=443; --This value originates from the main library and executes the position field after show master status

2.4 Querying from Library state

Start slave;

Show Slave Status\g

Three: Main Library SQL Test

The main library creates the database:

Create database copytest;

To Create a table :

Create table Tu (UID bigint primary key);

Add a piece of data

Insert into Tu (1);

Now let's look at the database from:

You can see that the data for the master library has been copied to the from library.

Five: Summarize the problem

1: Execute synchronous statement, error

Mysql> Change Master to
Master_host= ' 192.168.71.135 ',
Master_user= ' Syncuser ',
Master_password= ' 123456 ',
Master_log_file= ' mysql-bin.000001 ',
master_log_pos=443;

The following error occurred:

Slave is not configured or failed to initialize properly. You must at least set--server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.

Workaround:

Delete the slave MySQL library table

drop table Slave_master_info;
drop table Slave_relay_log_info;
drop table Slave_worker_info;
drop table innodb_index_stats;
drop table innodb_table_stats;

Restart slave database:/etc/init.d/mysql restart;

2: Configured, slave execute show slave status\g statement, slave_io_running:no show no

Slave_io_running:yes in order to copy normally, no can not be copied, you will see an error,

Last_io_error:fatal error:the slave I/O thread stops because master and slave have equal MySQL server UUIDs; These uuids must is different for replication to work.

It means that master and slave's UUID repeats, with show variables like '%server_uuid% ' query out UUID, obviously two machine's uuid, so there is the above error.

Because the master-slave Utunbu environment is cloned, so the problem arises, we enter the MySQL data directory, modify the Auto.cnf file, through show variables like '%datadir% ' query out the data directory.

Use vim auto.cnf to modify the auto.cnf file.

MySQL master-slave copy Notes