Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "Mysql master-slave installation configuration method". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Environment:

The MySQL version on the master and slave server is 5.1.34.

Host IP:192.168.0.1

Slave IP:192.168.0.2

one。 MySQL master server configuration

1. Edit configuration file / etc/my.cnf

# make sure it looks like the following www.2cto.com

Server-id = 1

Log-bin=-bin

Binlog-do-db=mysql # the name of the database that needs to be backed up. If you back up multiple databases, you can set this option repeatedly.

Binlog-ignore-db=mysql # does not need the database name to be backed up. If you back up multiple databases, you can set this option repeatedly.

The parameter log-slave-updates # must be added, otherwise the updated records will not be added to the binary file

Slave-skip-errors # skips the error and continues with the copy operation

two。 Establish a user

Mysql > grant replication slave on *. * to slave@192.168.0.2 identified by '111111'

# grant replication slave on *. * to 'username' @ 'host' identified by 'password'

# connection test can be done on Slave: mysql-h 192.168.0.1-u test-p

3. Lock the main database table

Mysql > FLUSH TABLES WITH READ LOCK

4. Display the main library information

Record File and Position, slave library settings will be used

=

Mysql > SHOW MASTER STATUS

+-+

| | File | Position | Binlog_do_db | Binlog_ignore_db | |

+-+

| | mysql-bin.000001 | 106 | |

+-+

5. Open another terminal and package the main library

Cd / usr/local/mysql # mysql library directory

Tar zcvf var.tar.gz var www.2cto.com

= =

II. MySQL slave server configuration

1. Transfer and unpack the data packets from the main library

# cd / usr/local/mysql

# scp 192.168.0.1:/usr/local/mysql/var.tar.gz.

# tar zxvf var.tar.gz

2. View and modify var folder permissions

# chown-R mysql:mysql var

3. Edit / etc/my.cnf

Server-id=2

Log-bin=mysql-bin

Master-host=192.168.0.1

Master-user=slave

Master-password=111111

Master-port=3306

Replicate-do-db=test # the name of the database to be backed up

Replicate-ignore-db=mysql # ignored databases

Master-connect-retry=60 # if the slave server finds that the master server is down, the time difference to reconnect (seconds)

The parameter log-slave-updates # must be added, otherwise the updated records will not be added to the binary file

Slave-skip-errors # skips the error and continues with the copy operation

4. Verify the connection to MASTER

# mysql-h292.168.0.1-uslave-ppassword

Mysql > show grants for slave@192.168.0.2

5. Set synchronization on SLAVE

Set the File,MASTER_LOG_POS connected to MASTER MASTER_LOG_FILE as the primary library as the Position of the primary library

= =

Mysql > slave stop

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.0.1',MASTER_USER='slave',MASTER_PASSWORD='111111',MASTER_LOG_FILE='

Mysql-bin.000001',MASTER_LOG_POS=106; www.2cto.com

6. Start the SLAVE service

Mysql > slave start

7. View SLAVE status

Mysql > SHOW SLAVE STATUS\ G

Where the values of the Slave_IO_Running and Slave_SQL_Running columns are both "Yes", indicating that the Slave's Imax O and SQL threads

It's all working normally.

8. Unlock the main library table

Mysql > UNLOCK TABLES

The master-slave library has been built successfully. You can insert data on the main library to test whether synchronization is normal.

Common mistakes and solutions:

Handling of frequently asked questions:

Www.2cto.com

1: show slave status\ G on the slave library; the following occurs

Slave_IO_Running: Yes

Slave_SQL_Running: No

Seconds_Behind_Master: NULL

Reason:

a. The program may have written on slave.

b. It may also be caused by the rollback of the transaction after the slave machine is restarted.

Solution:

Enter master

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000040 | 324 |

+-+

Then perform a manual synchronization on the slave server

Slave stop

Change master to

Master_host='10.14.0.140'

Master_user='repl'

Master_password='111111'

Master_port=3306

Master_log_file='mysql-bin.000040'

Master_log_pos=324

Slave start

Show slave status\ G

2. Phenomenon: unable to synchronize from database. Show slave status shows that Slave_IO_Running is No,Seconds_Behind_Master.

For null

Solution: restart the master database

Service mysql restart

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000001 | 98 | |

+-+

Slave stop

Change master to Master_Log_File='mysql-bin.000001',Master_Log_Pos=98

Slave start

Or something like this:

Stop slave

Set global sql_slave_skip_counter = 1

Start slave

This phenomenon is mainly due to the problem of master database. In practice, I can restart master and then restart slave to solve this problem.

If this problem occurs, you must restart the master database.

1. The synchronization of primary and secondary libraries is mainly achieved through binary logs.

two。 When starting the child library, you must first synchronize the data and delete the: master.info file in the log directory. Because master.info records

The last time you want to connect to the main library, if you don't delete it, it won't work even if it is modified in my.cnf. Because what is read is still

The information in the master.info file.

In a mysql replication environment, there are eight parameters that we can control. The DB or table that need to be replicated or ignored are:

The following two items need to be set on Master:

Binlog_Do_DB: set which databases need to record Binlog

Binlog_Ignore_DB: set where the database does not need to record Binlog

The advantage is that the amount of Io and the network IO caused by the Binlog recording on the Masterside are reduced, and the IO threads and SQL threads on the slave side are reduced.

As a result, replication performance is greatly improved.

The disadvantage is that mysql determines whether an event needs to be copied not based on the DB of the query that generated the event, but on the time when the query was executed

(that is, the library name specified when logging in or the DB specified in running "use database"), only in the current default DB and configuration

The I / O thread will read the event to the I / O thread of the slave only when the set DB matches exactly. So, if the default DB and settings need to be copied

When the DB is different, the data in a Table in the DB that needs to be copied is changed, and the event will not be copied to the Slave, so that

It will cause the data of Slave side to be inconsistent with that of Master. Similarly, the data in the database that does not need to be replicated is changed under the default database

Will be copied to the server, when the slave side does not have the database, it will cause replication error and stop.

The following six items need to be set on slave:

Replicate_Do_DB: sets the database to be replicated. Multiple DB are separated by commas.

Replicate_Ignore_DB: sets the database that can be ignored.

Replicate_Do_Table: set the Table to be copied

Replicate_Ignore_Table: sets the Table that can be ignored

Replicate_Wild_Do_Table: the function is the same as Replicate_Do_Table, but can be set with wildcards.

Replicate_Wild_Ignore_Table: function is the same as Replicate_Do_Table, function is the same as Replicate_Ignore_Table, and wildcards can be used.

The advantage is that the replication filtering mechanism is set up on the server to ensure that Slave and Master data will not be caused by default database problems.

Problems with inconsistencies or replication errors.

The disadvantage is that the performance is worse than on the Master side. The reason is that events are read to Slave by the IO thread regardless of whether they need to be copied or not.

This not only increases the amount of network IO, but also increases the amount of Relay Log writing to the I O thread on the Slave side.

Explanation of synchronization principle

MySQL's Replication is based on the primary server tracking all changes to the database (updates, deletions, etc.) in the binary log.

MySQL uses three threads to complete Replication work. The specific distribution is one related thread on the master and two related threads on the slave.

The related threads of the master can understand that the Binlog Dump thread and the slave server in the output of the SHOW PROCESSLIST on the master server are IO and

SQL thread

The master server creation sends the contents of the binlog to the slave server. That is sent by the master server Binlog Dump thread is read from the server ID O thread

Content and copy the data to the relay log file (relay-log) in the data directory of the slave server. The SQL thread is used to read the relay log.

And perform the updates contained in the log.

The Replication of MySQL is one-way, asynchronous synchronous.

The MySQL synchronization mechanism records all updates, deletions, etc., to the database based on master in the binary log. Therefore, you want to enable synchronization

Mechanism, binary logging must be enabled in master. Each slave accepts updates from the binary log recorded on the master

So a copy of this operation is performed on slave. It is important to realize that binary logging only starts from enabling binary logging

The update operation is recorded at the beginning. All slave must copy the data that already exists on the master when binary logging is enabled.

If the data on slave when synchronization is running is inconsistent with that when binary logging is enabled on master, then slave synchronization will fail.

One of the ways to copy data from master is to execute LOAD DATA FROM MASTER statements on slave. Note, however, that LOAD DATA FROM MASTER has been available since MySQL 4.0.0, and only supports MyISAM type tables on master. Similarly

This operation requires a global read lock so that there is no update operation on the master when the log is sent to slave. When it comes true

When free lock tables are hot backed up (in MySQL 5. 0), global read locks are not necessary. Because of these limitations, we recommend that only on master

Execute the LOAD DATA FROM MASTER statement only when the relevant data is small, or allow a long read lock on the master.

Since the speed of LOAD DATA FROM MASTER varies from system to system, a better measure rule is to copy 1MB data per second.

This is only a rough estimate, but both master and slave are Pentium 700MHz machines and can do this when connected to the 100MBit/s network.

Speed. Once the master data has been fully copied on the slave, you can connect to the master and wait for updates to be processed. If master

When the computer crashes or the slave connection is disconnected, slave periodically attempts to connect to the master until it can reconnect and wait for updates. The interval between retries is controlled by the-master-connect-retry option, which defaults to 60 seconds. Each slave records the location of the log when it was closed.

Master does not know how many slave connections or which slave will be updated.

The MySQL synchronization function is implemented by 3 threads (1 on master and 2 on slave). After the START SLAVE statement is executed, slave creates the

An Ithumb O thread. The Icano thread connects to the master and requests the master to send the statements in the binary log. Master creates a thread to set the

The contents of the log are sent to slave. The Binlog Dump in the result after this thread executes the SHOW PROCESSLIST statement on master

The thread is. The I / O thread on slave reads the statements sent by the Binlog Dump thread of master and copies them to its data directory

In the relay log (relay logs) under. The third is the SQL thread, which salve uses to read the relay logs and then execute them to update the data.

As mentioned above, there are three threads on each mster/slave. There are multiple threads on each master, which creates one thread for each slave connection, and each slave has only Icano and SQL threads. Before MySQL 4.0.2, synchronization required only two threads (one for master and one for slave). ICando on slave

Merged with the SQL thread, which does not use relay logs. The advantage of using 2 threads on slave is to separate log reading and execution into 2

An independent mission. If the execution of the task is slow, the log reading task will not slow down. For example, if slave stops for a period of time, then

The SQL O thread can read all the logs from the master quickly after the slave starts, although the SQL thread may lag several hours behind the Imap O thread.

If slave stops before the SQL thread has finished executing, but the Imax O thread has read and saved all the update logs locally.

They are in the relay-log, so they will continue to be executed after slave starts again. This allows binary to be cleared on master

Log, because slave no longer needs to go to master to read the update log. Executing the SHOW PROCESSLIST statement tells us what is happening on master and slave that we are concerned about.

This is the end of the content of "Mysql master-slave installation configuration method". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.