"Reprint" MySQL master server configuration

Summary: 1, configure the master server 2, configure the server 3, master and slave server Status View and problem handling

MySQL master-Slave synchronization architecture is currently a popular database architecture, using MySQL master-slave configuration, can achieve read and write separation, reduce the main database access pressure, improve website performance. The basic principles of MySQL's master-slave server are as follows:

Describe the process roughly: from the server's IO thread, get the binary log from the primary server, save it locally as a trunk log, and then execute the contents of the relay log from the top through the SQL thread to keep it consistent from the library and the main library. The detailed process of master-slave synchronization is as follows:

1. The primary server verifies the connection.

2. The primary server opens a thread from the server.

3. Shift the primary server log from the server to the primary server.

4. The primary server checks to see if the value is less than the current binary log bias shift.

5. If it is less than, notify the server to fetch the data.

6. From the server continues to fetch data from the primary server until the completion, at this time, from the server thread into sleep, the primary server thread into sleep.

7. When the primary server has an update, the primary server thread is activated and the binary log is pushed to the slave server, and the server thread is notified to enter the working state.

8. Executes the binary log from the server SQL thread and then goes to sleep.

For more information, refer to: http://blog.chinaunix.net/uid-20639775-id-3254611.html

For example now we have two servers, 172.17.22.187 and 172.17.22.188, now the 188 server MySQL database with the primary server, 187 database with the slave server (Slave), of course, Slave can have multiple. MySQL master server general use method is responsible for the nonquery operation of the Web site, from the server (Slave) is responsible for query operation, we can according to the features of the website function module to specify the access to the server, such as the list of foreground items, Investment and financing records, such as the query module can specify access to slave, bids, project audits and other transactional operations access to Mater, can also write a pool or queue, free allocation of requests from the server connection.

1. Configuring the master server

The principle of master-slave synchronization above can be understood as the main from the server through the IO thread on the Binary log copy, and the replay of the SQL statement to achieve data synchronization, of course, when copying the primary server return information in addition to the information contained in the log, the return of the information on the master side of the binary Log file name and location in Binary log, more information on this process can be found on "MySQL Replication thread", such as:

http://blog.csdn.net/bengda/article/details/7852889

In the above summary of Rights Management also mentioned, there is a "REPLICATION SLAVE" permission, which is the replication permissions in server management, with this permission SLAVE to copy Binlog logs from the master server. So we need to create a database user and give him replication permissions, create and empower SQL as follows:

GRANT REPLICATION slave,file on * * to ' repl ' @ ' 172.17.%.% ' identified by ' 123456 ';

After the role has been created, to add the Log-bin configuration on the master server configuration file, turn on binary log function, otherwise cannot realize replication copy, the path of the configuration file is/usr/my.cnf, different machine or system may not be the same path, you can use Find/ -name my.cnf Find out, find and modify add the following content:

Server-id=1

Log-bin=master-bin

Log-bin-index=master-bin.index

Server-id master server with 1, from the server configuration should be careful not to conflict with the main server, or the time will appear Mo people its wonderful problem, because the synchronization will be based on the Server-id do judge, if the Server-id is not synchronized, MY.CNF other configuration of the introduction can refer to: http://database.51cto.com/art/201108/285365.htm

It is important to note that the MySQL service must be restarted each time the configuration file is modified to take effect:

Service MySQL restart;//restart MySQL services

Before synchronizing, we can back up the data inside the original repository to the slave library, and we can use mysqldump, the syntax is as follows:

Mysqldump-uroot-p--lock-tables--events--triggers--routines--flush-logs--master-data=2--databases authority_test rep_test_db Replication > Db.sql

--databases followed by authority_test, etc. as the database, save their snapshots to the Db.sql file.

After you move the Db.sql file to the slave server, you can use: Mysql-uroot-p < Db.sql to back up the data contents of the main library to the slave library.

2. Configure the slave server

1) Locate the configuration file from the server my.cnf, and add the following configuration:

server-id=2

Relay-log-index=slave-relay-bin.index

Relay-log=slave-relay-bin

2) Restart the MySQL service from server: services MySQL restart.

3) Log in from the server database, to connect to master, use the following sql:

Change Master to master_host= ' 172.17.22.188 ', master_port=3306, master_user= ' repl ', master_password= ' 123456 ', master_ Log_file= ' master-bin.000001 ', master_log_pos=0;

4) Use SQL syntax in MYSQ: Start slave. Start slave

3, master and slave server Status View and problem handling

You can use show slave status\g to view the running status of slave:

In the state of slave status, there are two items: Slave_io_running and slave_sql_running, which must be Yes to indicate normal operation.

If it is slave_io_running no, then I personally see there are three kinds of situations, one is the network has a problem, the connection is not, the second is likely to my.cnf a problem.

Once IO is no, first look at the Err log, see what is wrong, it is likely that the network, it is also possible that the package is too large to receive, this time from the machine to change max_allowed_packet this parameter.

When the slave_sql_running is No:

Workaround One

1. The program may have been written on the slave

2. It is also possible that the transaction rollback is caused by the slave machine being reset.

This is typically caused by a transaction rollback:

Workaround:

mysql> slave stop;

Mysql> set GLOBAL sql_slave_skip_counter=1;

mysql> slave start;

It is recommended to use this method.

Set GLOBAL sql_slave_skip_counter=n, which is used to skip one or N error copy statements from the standby machine.

There will be a statement in show slave status that is faulted.

Set GLOBAL sql_slave_skip_counter=1 will find that a statement has been skipped once.

If you have finished setting up, you will see an error statement. To perform more than a few times.

Solution II

First stop the slave service: Stop slave.

To view host status on the primary server:

Note the value corresponding to file and position

Login Master

Use SQL syntax: Show master status to view the master run status:

Then perform a manual synchronization on the slave server:

Change Master to Master_host = ' 172.17.22.188 ', master_port = 3306,master_user = ' repl ', master_password= ' 123456 ', master_ Log_file= ' master-bin.000007 ', master_log_pos=120;

Relocate log files and log locations.

Then: Start slave, launch slave.

After the problem is finished, you can try to do a variety of additions and deletions on master, you can find that the slave database will be updated synchronously, consistent with master.

copyright belongs to the author.

"Reprint" MySQL master server configuration