Shulou(Shulou.com)06/01 Report--

Prepare the server

Since the (binary log) binlog format may be different between different versions of Mysql, the best combination is that the Mysql version of the master server and the slave server version are the same or lower, and the master server version must not be higher than the slave server version.

Both of the server versions I tested this time are Mysql-5.5.17.

Mysql establishes master-slave server dual hot standby configuration steps

2.1 description of the environment

A server (master server Master): 59.151.15.36

Server B (slave server Slave): 218.206.70.146

The Mysql version of the master and slave servers is 5.5.17.

Under the Linux environment

Backup a copy of the database content that the master server needs to synchronize and upload it to the slave server to ensure that the database contents of the two servers are consistent at the beginning.

However, it is explained here that since I tested using the database test that Mysql had after installation, there are no tables created in the two servers, only the same empty table tb_mobile is created in test.

The Sql statement is as follows:

Mysql > create table tb_mobile (mobile VARCHAR (20) comment' Mobile number', time timestamp DEFAULT now () comment' time')

2.2 Master server Master configuration

2.2.1 create a synchronization user

Enter the mysql interface and establish a connection account on the master server for the slave server, which must be granted REPLICATION SLAVE permissions. Because since mysql version 3.2, it can be operated as a dual-computer hot backup through REPLICATION.

The operation instructions are as follows:

Mysql > grant replication slave on. To 'replicate'@'218.206.70.146' identified by' 123456'

Mysql > flush privileges

After creating the synchronous connection account, we can see if the connection is successful by accessing the master server (Master) database with the replicat account on the slave server (Slave).

Enter the following instructions on the slave server (Slave):

[root@YD146] # mysql-h69.151.15.36-ureplicate-p123456

If the following result appears, it means that the login is successful, which means that the two servers can be operated by dual-server hot backup.

2.2.2 modify mysql configuration file

If the above preparations are done, we can modify the mysql configuration file there. first of all, we can find all the mysql configuration directories. Generally, after installing the mysql service, we will copy the configuration files one by one and put them under the / ect directory, and the configuration file will be named: my.cnf. That is, the exact directory of the configuration file is / etc/my.cnf

(MySQL installed with rpm package under Linux will not install the / etc/my.cnf file

As to why MySQL can start and function normally without this file, there are two theories at this point.

First, my.cnf is just a parameter file when MySQL starts. Without it, MySQL starts with the built-in default parameters.

Second, MySQL automatically uses the my-medium.cnf file in the / usr/share/mysql directory when it starts, which is limited to the MySQL installed by the rpm package

The solution is simply to copy a my-medium.cnf file from the / usr/share/mysql directory to the / etc directory and rename it to my.cnf.)

After finding the configuration file my.cnf and opening it, modify it under [mysqld]:

[mysqld]

Server-id = 1 / / unique id

Log-bin=mysql-bin / / these two lines already exist, so you can add the following two lines without moving. Specify log file

Binlog-do-db = test / / the database that logs

Binlog-ignore-db = mysql / / databases that do not log

2.2.3 restart the mysql service

After modifying the configuration file and saving it, restart the mysql service. If it is successful, it will be fine.

2.2.4 View the status of the primary server

After entering the mysql service, you can view the Master status through instructions and enter the following instructions:

Note that the parameters, especially the first two File and Position, will be useful in configuring the master-slave relationship on the slave server (Slave).

Note: the lock table is used here in order to generate no new data in the environment, so that the synchronization location can be located from the server, and remember to unlock it after the first synchronization is completed.

2.3 Slave configuration from server

2.3.1 modify the configuration file

Because this is a master-slave way to achieve mysql dual hot backup, so in the slave server does not need to establish a synchronization account, directly open the configuration file my.cnf to modify, the reason is still the same as modifying the master server, but the parameters that need to be modified are not the same. As follows:

[mysqld]

Server-id = 2

Log-bin=mysql-bin

Replicate-do-db = test

Replicate-ignore-db = mysql,information_schema,performance_schema

2.3.2 restart the mysql service

After modifying the configuration file and saving it, restart the mysql service. If it is successful, it will be fine.

2.3.3 specify the synchronization location with the change mster statement

This is the most critical step. After entering the mysql interface, enter the following instructions:

It is important that mysql > stop slave; / / stop the slave service thread first, because failure to do so will make the following operations unsuccessful.

Mysql > change master to

Master_host='59.151.15.36',master_user='replicate',master_password='123456'

Master_log_file=' mysql-bin.000016', master_log_pos=107

Note: master_log_file and master_log_pos are determined by the status values found by the master server (Master). That's what I just called attention. Master_log_file corresponds to File and master_log_pos corresponds to Position. Mysql versions 5.x and above no longer support specifying options related to the primary server in the configuration file.

For the problems encountered, if the following occurs after following the above steps:

You need to reset the slave. The instructions are as follows

Mysql > stop slave

Mysql > reset slave

Then stop the slave thread and start over. After success, you can start the slave thread.

Mysql > start slave

2.3.4 View slave server (Slave) status

View with the following instructions

Mysql > show slave status\ G

Check that both of the following values are Yes, which means that the setting is successful from the server.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

2.4 Test synchronization

As mentioned earlier, there is only one table in the database test, tb_mobile, which has no data. We can first check whether the databases of the next two servers have data:

Master:59.151.15.36

Slave:218.206.70.146

Well, now you can insert data into the Master server to see if it can be synchronized.

Master:59.151.15.36

Slave:218.206.70.146

As can be seen from the above two screenshots, the data inserted on the Master server can be found on the Slave server, which means that the dual-server hot backup configuration is successful.

Steps for Mysql to establish a master-master server hot standby configuration

The server is still used back to the current two servers.

3.1 create a synchronization user

At the same time, a connection account is established on the master-slave server, which must be granted REPLIATION SLAVE permission. Here, because server An and server B are master and slave to each other, each has to establish a synchronous user.

Server A:

Mysql > grant replication slave on. To 'replicate'@'218.206.70.146' identified by' 123456'

Mysql > flush privileges

Server B:

Mysql > grant replication slave on. To 'replicate'@'59.151.15.36' identified by' 123456'

Mysql > flush privileges

3.2 modify the configuration file my.cnf

Server A

[mysqld]

Server-id = 1

Log-bin=mysql-bin

Binlog-do-db = test

Binlog-ignore-db = mysql

# main-additional parts to be added in the main form

Log-slave-updates

Sync_binlog = 1

Auto_increment_offset = 1

Auto_increment_increment = 2

Replicate-do-db = test

Replicate-ignore-db = mysql,information_schema

Server B:

[mysqld]

Server-id = 2

Log-bin=mysql-bin

Replicate-do-db = test

Replicate-ignore-db = mysql,information_schema,performance_schema

# main-additional parts to be added in the main form

Binlog-do-db = test

Binlog-ignore-db = mysql

Log-slave-updates

Sync_binlog = 1

Auto_increment_offset = 2

Auto_increment_increment = 2

Restart the mysql service on server An and server B respectively

Restart the server in the same way as above, so I won't explain it here.

3.4 check the status of server An and server B as master servers respectively

Server A:

Server B:

3.5 specify synchronization locations with change master to on A server and B server, respectively

Server A:

Mysql > change master to

Master_host='218.206.70.146',master_user='replicate',master_password='123456'

Master_log_file=' mysql-bin.000011', master_log_pos=497

Server B:

Mysql > change master to

Master_host='59.151.15.36',master_user='replicate',master_password='123456'

Master_log_file=' mysql-bin.000016', master_log_pos=107

3.6 restart the slave service thread on server An and server B, respectively

Mysql > start slave

3.7 View slave server status on An and B servers, respectively

Mysql > show slave status\ G

Check that both of the following values are Yes, which means that the setting is successful from the server.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

3.8 Test Master-Master synchronization example

Test Server A:

Insert a statement on server An as shown in the following figure:

Then check on server B to see if synchronization is as shown in the following figure:

Test Server B:

Insert a statement on server B as shown in the following figure:

Then check from server A to see if there is any synchronization data, as shown in the following figure:

Finally, it can be seen from the results that the master-master form of dual-computer hot standby can be realized successfully.

Configuration parameter description

Server-id

The ID value uniquely identifies the master and slave servers in the replication cluster, so they must be different. The Master_id must be a positive integer value between 1 and 232mur1, and the slave_ id value must be a positive integer value between 2 and 232mur1.

Log-bin

Means to open binlog, turn on this option, you can write to the relay-log of Slave through I _ replication O, which is also a prerequisite for replication.

Binlog-do-db

Represents a database that needs to record binary logs. If you have multiple data, you can separate them with commas, or use multiple binlog-do-dg options.

Binglog-ingore-db

Represents a database that does not need to record binary logs, if there are multiple databases that can be separated by commas, or use the multi-binglog-ignore-db option.

Replicate-do-db

Represents a database that needs to be synchronized, if there is more than one data that can be separated by commas, or if multiple replicate-do-db options are used.

Replicate-ignore-db

Represents databases that do not require synchronization, if there are multiple databases that can be separated by commas, or if multiple replicate-ignore-db options are used.

Master-connect-retry

Master-connect-retry=n indicates that the connection between the slave server and the master server is not successful, then wait n seconds (s) before managing (the default setting is 60s). If a mater.info file exists from the server, it ignores some options.

Log-slave-updates

Configure whether the update operation on the slave library is written to a binary file, and if this slave library needs to be the master library of other slave libraries, you need to type this parameter so that the slave libraries of the slave library can perform log synchronization.

Slave-skip-errors

During the replication process, the sql in binglo goes wrong due to various reasons. By default, replication from the slave library is stopped and the user is required to intervene. You can set slave-skip-errors to define the error number, and if the error encountered during replication is a defined error number, you can pass by. If the slave library is used for backup, setting this parameter will cause data inconsistency, so do not use it. If it is to share the query pressure of the main database, it can be considered.

-- slave-skip-errors= [err _ code1,err_code2,... | all | ddl_exist_errors]

Command-Line Format-slave-skip-errors=name

Option-File Format slave-skip-errors

System Variable Name slave_skip_errors

Variable Scope Global

Dynamic Variable No

Permitted Values

Type string

Default OFF

Valid Values OFF

[list of error codes]

All

Ddl_exist_errors

MySQL 5.6 as well as MySQL Cluster NDB 7.3 support an additional shorthand value

Ddl_exist_errors, which is equivalent to the error code list 1007,1008,1050,1051

1054,1060,1061,1068,1094,1146.

Examples:

-- slave-skip-errors=1062,1053

-- slave-skip-errors=all

-- slave-skip-errors=ddl_exist_errors

Sync_binlog=1 Or N

The default value of Sync_binlog is 0, and in this mode, MySQL is not synchronized to disk. In this way, Mysql relies on the operating system to refresh the binary log binary log, just like the operating system flushes other files. So if the operating system or machine (not just the Mysql server) crashes, it is possible that the last statement in the binlog is lost. To prevent this, you can use the sync_binlog global variable to synchronize binlog with the hard disk after every N binlog writes. It is safest when the sync_binlog variable is set to 1, because in the event of a crash crash, your binary log binary log can only lose at most one statement or transaction. However, this is also the slowest way (unless the disk has a cache cache with battery backup power, which makes synchronizing to disk very fast).

Even if sync_binlog is set to 1, it is possible that there is an inconsistency between the table content and the binlog content in the event of a crash. If you use the InnoDB table, the Mysql server processes the COMMIT statement, which writes the entire transaction to binlog and commits the transaction to InnoDB. If a crash occurs between operations, the transaction is rolled back by the InnoDB on restart, but still exists in the binlog. You can use the-innodb-safe-binlog option to increase consistency between InnoDB table contents and binlog. (note: this option is not needed in Mysql version 5.1-innodb-safe-binlog; is invalidated due to the introduction of XA transaction support.) this option provides greater security so that the binlog (sync_binlog=1) and (true by default) InnoDB logs for each transaction are synchronized with the hard disk. The effect of this option is that when you restart after a crash, the Mysql server cuts the rolled InnoDB transactions from binlog after the transaction is rolled back. This ensures that binlog feeds back the exact data of the InnoDB table, and so on, and keeps the slave server synchronized with the master server (no rollback statements are received).

Auto_increment_offset and Auto_increment_increment

Auto_increment_increment and auto_increment_offset are used for master-master server (master-to-master) replication and can be used to control the operation of AUTO_INCREMENT columns. Both variables can be set to global or local variables, and each value is assumed to be an integer value between 1 and 65535. Setting one of the variables to 0 makes the variable 1.

These two variables affect how the AUTO_INCREMENT column is done: auto_increment_increment controls the increment of the value in the column, and auto_increment_offset determines the starting point of the AUTO_INCREMENT column value.

If the value of auto_increment_offset is greater than the value of auto_increment_increment, the value of auto_increment_offset is ignored. For example, if you already have some data in the table, you will use the maximum self-increment that you already have as the initial value.

How to solve the wrong SQL of MySQL master-slave synchronization

Resolve:

Stop slave

# means to skip a step error, and the following number is variable

Set global sql_slave_skip_counter = 1

Start slave

Then use mysql > show slave status\ G to view:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Ok, now the master-slave synchronization status is normal.

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.