MySQL DATA master-slave synchronization Configuration

Because you need to configure the master-slave synchronization configuration of MySQL, the configuration process is now recorded and is not required in the future.

MySQL DATA Master/Slave synchronization 1.1. Synchronization Introduction

The master-slave synchronization of MySQL is an asynchronous replication process, which is replicated from one master to another. The entire replication process between the master and slave is mainly completed by three threads, two of which (SQL thread and IO thread) are on the slave side, and the other (IO thread) on the master side.

To achieve MySQL master-slave synchronization, you must first enable the binarylog (MySQL-bin) function on the master side. Otherwise, it cannot be implemented. The whole replication process is actually because slave obtains the log from the master end and then executes the operations recorded in the log in full order on itself. To enable the binary log of MySQL, you can use the "-log-bin" parameter option during MySQL server startup, or. the "Log-bin" parameter item is added to the mysqld parameter group (the parameter section marked by [mysqld]) in the CNF configuration file.

1.2. MySQL Master/Slave Server Configuration

Master Server IP Address: 192.168.1.60

Slave service IP: 192.168.1.61

Linux: centos

MySQL version: 5.1.58

The following configurations are configured on this service:

I. primary database operations

View the my. CNF configuration file of the master database server. The path and configuration file path vary depending on the MySQL installation package and version, but most of the cases are the same. The file is in the ETC/directory. MySQL also provides different configuration files for different server configurations. to use them, you only need to replace the my. CNF File above with either of them. Note: The replacement file name must be my. CNF.

There are usually four CNF configuration files, which can be viewed by the command # ll/usr/share/MySQL/*. CNF;

My-small.cnf memory is less than or equal to 64 m, only a small number of database services;

32 m-64 m in the my-medium.cnf and used with other services, such as web;

My-large.cnf memory has m mainly to provide database services;

My-huge.cnf memory has 1g to 2G, mainly provide database services;

My-innodb-heavy-4G.cnf memory 4 GB, mainly to provide a large load Database Service (General servers use this );

You can select different CNF configuration files based on different server configurations.

 

1. Configure the my. CNF File

Use the default ETC/My. configure the CNF configuration file. to configure the master server, add the following items to the configuration file (ensure that they do not exist in the file when adding them, because if you select the above several different configuration files, the content in the configuration will be different. If there is no corresponding item, you need to add it ).

Use vi etc/My. CNF to open the file, modify the file, and add and modify the file under [mysqld:

Server-id = 1# This is the database ID. This ID is unique. The default value of the master database is 1. Other slave databases increase progressively with this ID. The id value cannot be repeated; otherwise, a synchronization error occurs;

Log-bin = mysql-binBinary log file. This parameter is required. Otherwise, data cannot be synchronized;

BINLOG-do-DB = testcreate# Databases to be synchronized. If you need to synchronize multiple databases;

To add this item.

BINLOG-do-DB = testcreate1

BINLOG-do-DB = testcreate2

BINLOG-ignore-DB = MySQLDatabases that do not need to be synchronized;

Now the configuration of my. CNF on the master server is complete, and the exit file is saved.

Restart the MySQL server now: # If servicemysqld restart fails, the configuration file is incorrect and you need to re-check it.

Suggestion: We recommend that you back up my. CNF before modifying it to avoid the failure of configuration file modification and the failure to restart the server in time.

2. After configuration, you need to create a database account for synchronization.

The master database must provide an account for connecting and synchronizing data from the slave database, and use the command MySQL Server

Mysql> grantreplication slave on *. * To 'testcreate' @ '192. 168.1.61 'identified by '123 ';

Description: 'testcreate': the database to be synchronized;

'1970. 168.1.61 ': Synchronized database address;

'000000': the password of the synchronized database, which must be set during slave database configuration.

3. Display master database information

Mysql> showmaster status;

Execute the preceding command to display:

If the preceding information is displayed, the master database is successfully created.

4. Back up the database (this operation is not required if the slave server also creates a new database)

There are many ways to back up the database: If you can copy the database directly on the same LAN, you can also use tools to directly import data.

To copy a database, use the following packaging method:

# Cd/data to the path where the MySQL database is stored

# Tarcvfz testcreate.tar.gz dB/

Copy and decompress the package from the database.

# Cd/Data

# Scp192.168.1.61:/data/testcreate.tar.gz

# Tarxvfz testcreate.tar.gz

 

The data has been copied;

Ii. Slave Database Configuration

The configuration of the slave server is similar to that of the master database. The selected slave database server is 192.168.1.61.

1. Configure the slave database service my. CNF File

# Modify vietc/My. CNF as follows:

Server-id = 2# Here, the ID is changed to 2 because the master database is 1;

Log-bin = mysql-binRequired for data synchronization;

Master-host = 192.168.1.60Master database IP address;

Master-user = testAccount used for synchronization;

Masters-Password = 123456Synchronize the account password, which is set when the master database is used;

Master-Port = 3306The Port Number of the database to be synchronized.

2. Restart the slave Database Server

# Service mysqld restart

If the restart fails, the configuration file is faulty and you need to re-check the configuration.

3. Adjust the permissions of the copied database (if there is a database copied from the master database)

All Database permissions copied from the master database are root permissions and must be changed to MySQL permissions.

# Chown-r MYSQL: MySQL/var/lib/MySQL

This step is not required if the slave database is a new database.

4. Display slave server status

MySQL Server:

Mysql> showslave status \ G; Display

If both slave_io_running and slave_ SQL _running are yes, the slave server is configured successfully.

If you encounter position-related errors, You need to manually set the following file/postion information.

Mysql> changemaster to master_host = '192. 168.1.60'

Master_user = 'test'

Master_password = '000000'

Master_log_file = 'mysql-bin.000001 ', file information of the master database

Master_log_pos = '123'; position information of the master database

Iii. Master/Slave server test

Create the same database on the master server and slave server: testcreate table is: Test, field is ID, name

Add data to the master server

The testcreate database in 192.168.1.61 is displayed

The Master/Slave server is successfully synchronized.

Iv. FAQs

When configured as a server, the slave database cannot be modified, that is, the slave database has only the read permission. If the slave database is modified, synchronization will fail: If the slave database fails, use showslave status \ G; to view the slave server status

If slave_io_running: No, the connection from the database fails.

Last_error: the error log is displayed.

The main cause of this problem is transaction rollback. The solutions are as follows:

1,

Mysql> slave stop; stop slave Service

Mysql> set global SQL _slave_skip_counter = 1;

Mysql> slave start; start the slave Service

2. manually reset the slave server

Mysql> changemaster

Master_host = '192. 168.1.60'

Master_user = 'test'

Master_password = '000000'

Master_log_file = 'mysql-bin.000001 '; file information of the master database

Master_log_pos = '123'; position information of the master database

The MySQL Master/Slave service has been configured.

In the master server, data is mainly written. We recommend that you use the InnoDB Data Engine. In terms of reading, the MyISAM engine in MySQL is very efficient.

The MyISAM engine supports full-text indexing, but does not support transactions. InnoDB is the opposite!