Mysql master-slave copy notes, mysql master-slave notes

Mysql master-slave copy notes, mysql master-slave notes

I. Introduction to the test environment

The master-slave replication test environment is ubuntu + mysql5.7, the master server ip address is 192.168.71.135, the slave server ip address is 192.168.71.20., and the ubuntu environment is cloned from a server with a configured environment.

Ii. mysql Master/Slave Configuration

1 :) master Configuration

A. In the mysql directory (/etc/mysql), add the 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

Servers-id = 100

After setting, use show master status to query the status.

Set the account and password for connecting to the slave server

Configure a connection user

Grant replication slave on *. * to 'mysql account' @ '%' identified by 'Password'; this is a test and no slave server ip address is specified. to specify an ip address, run the following command:

Grant replication slave on *. * to 'mysql account' @ '192. 168.71.20.' identified by 'Password ';

After configuration, record and restart mysql.

2 :) configure slave Database

2.1 The Slave configuration is similar to that of the master (configuration file name: mysql. cnf, in the/etc/mysql directory, your environment may not be the Directory), you still need to restart the slave MySQL after the configuration is complete. 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_id is required and unique. Slave does not need to enable binary logs, but in some cases, it must be set. For example, if slave is another slave master, bin_log must be set. Here, we enable binary logs and display the name (the default name is hostname, but if the hostname is changed, the problem will occur ).
Relay_log configures the relay log. log_slave_updates indicates that slave writes the replication event into its own binary log (which will be useful later ).
Some people have enabled the slave binary log, but have not set log_slave_updates, and then check whether the slave Data has changed. This is an incorrect configuration. Therefore, use read_only whenever possible, which prevents data changes (except for special threads ). However, read_only is very useful, especially for applications that need to create tables on slave.

 

2.2 restart mysql after configuration is complete

/Etc/init. d/mysql restart

2.3 execute a synchronous SQL statement

Mysql> change master
Master_host = '1970. 168.71.135 ',
Master_user = 'syncuser ',
Master_password = '000000 ',
Master_log_file = 'mysql-bin.000001 ', -- the value is from the File field after the show master status Command is executed in the master database.
Master_log_pos = 443;-- The value is from the position field after the show master status Command is run on the master database.

2.4 query slave database status

Start slave;

Show slave status \ G

 

Iii. Primary Database SQL Testing

Create a database for the master database:

Create database copytest;

Create a table:

Create table tu (uid bigint primary key );

Add a data entry

Insert into tu (1 );

Now let's look at the database:

We can see that the data in the master database has been copied to the slave database.

V. Summary

1: An error occurred while executing the synchronization statement.

Mysql> change master
Master_host = '1970. 168.71.135 ',
Master_user = 'syncuser ',
Master_password = '000000 ',
Master_log_file = 'mysql-bin.000001 ',
Master_log_pos = 443;

The following error occurs:

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.

Solution:

Delete the table of the mysql database of slave

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 the slave Database:/etc/init. d/mysql restart;

2: After slave executes the show slave status \ G statement, slave_io_running: no indicates no.

Slave_io_running: yes can be copied normally. If it is no, it cannot be copied. You can 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 be different for replication to work.

This indicates that the uuid of the master and slave is repeated. You can use show variables like '% server_uuid %' to query the uuid. Obviously, the uuid of the two machines is the same, so the above error occurs.

Because the master-slave utunbu environment is cloned, this problem occurs. We enter the mysql data DIRECTORY and modify auto. in the cnf file, use show variables like '% datadir %' to query the data directory.

 

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