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

This article mainly introduces how to achieve MySQL database synchronization, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

MySQL data synchronization, in the MySQL official website documents, called Replication literally means redo, free translation means synchronization. In fact, the synchronization of MySQL does not use the word synchronous sync but redo replication, which accurately indicates that the essence of MySQL database operation is to do the same operation, or to repeat the same operation, in order to keep the data of the master database server master and the slave server slave consistent. Replication means to repeat and redo.

MySQL must open the bin-log key in order to implement replication, as well as the binary MySQL logging option.

MySQL's bin log binary log records all sql operations that affect the contents of records stored in database tables, such as insert / update / delete operations, without recording operations such as select. Therefore, we can restore the data lost in a certain period of time to the database through the binary log (if the log entry recorded in the binary log contains all the data in the database table, then, you can recover all the data in the local database). And this binary log, if used for remote database recovery, is replication. This is why you use replication instead of sync. That's why you set the bin-log = option.

In the synchronization process, the most important synchronization reference is to synchronize which binary log file is used and start synchronization from that record.

Next, let's explain the core process:

First of all, you should have two or more MySQL database servers, preferably version 3.3 (of course, two servers are not necessarily two machines, it is possible to install two MySQL services on one machine, and if you are proficient in MySQL replication principles, you can even synchronize between two different database database of a MySQL service See if there is any need) description: these two servers are generally set up as a master server, or source server, master mysql server, and the other or more replication slave synchronization slave servers. One slave is set up in the same way as multiple slave, so you can do something like a database cluster.

Set up an accessible MySQL account, and the operation is in English.

MySQL accounts are generally set to limit IP access to ensure security

The MySQL account is generally set to the same account as slave in master, and can be accessed remotely

In particular, if you are using the Linux / Unix operating system, be sure to pay attention to whether the firewall firewall restricts MySQL remote access. If so, it is best to open the remote access port and make access IP restrictions.

Since the MySQL account password is to be stored in clear text in my.cnf, please be careful to protect my.cnf from other users (it seems that you need to explain to MySQL that you will save passwd with password later).

The third step, of course, is to set the database that the two servers want to synchronize to the same database.

Here are some tips, including mysql's sql instruction instructions.

One of the methods, as mentioned in the English description, first lock the database read and write function (in fact, it is best to stop the mysqld service, and then make a backup), and then use tar to back up the database directory and go to the same data directory on the slave server.

Second, use a utility like mysql studio and directly use mysql studio's backup database tool to synchronize the database.

The above two methods are that there are many data records in master database. According to the above two methods, master and slave have the same database and data records. The third method, on the other hand, is suitable for creating a new database, especially when master and slave have set up a replication relationship in my.cnf (but no database synchronization database is specified): this is the use of mysql's sql statements load table from master and load data from master

Load table from master can copy the table structure from the master database to the slave database, thus creating synchronized tables.

Load data from master imports data from the master database into the slave data table, provided that master uses the bin-log parameter from the beginning of installation and operation and keeps a binary log.

The next step is to configure the my.cnf files for master and slave so that replcation can have appropriate startup parameters to support data synchronization

Tip: if you use win2k's mysql, you can mysql.com the official mysql administrator utility and configure master and slave directly in mysql administrator, as well as query-cache.

Added to master's my.cnf (or my.ini if it's win32)

[mysqld]

Log-bin =

Server-id=1

Note that the equal sign in log-bin = above is indispensable.

Modify the my.cnf in slave

[mysqld]

Server-id=2 # if you have more than one slave, change it to a non-repetitive id. In mysql 4.1, this has been cancelled.

Master-host=10.10.10.22

Master-user=backup # synchronize user accounts

Master-password=1234

Master-port=3306

Master-connect-retry=60 # default retry interval of 60 seconds

Replicate-do-db=test # tells slave to update only the test database

Bin-log =

Check the configuration status of master and slave, and use the two SQL directives show master status; and show slave status; to view the configuration status in master and slave. Here are two state variables that are very important

Mysql > SHOW MASTER STATUS

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.003 | 73 | test | manual,mysql | |

+-+

Note that File is mysql-bin.003 and position is 73

This file is a binary log record of the test database. The current number of records for data changes is 73.

As we mentioned earlier, the binary log records all the sql operation statements that record changes in a database, such as insert / update / delete, etc. Because of this, when slave synchronizes the mysql database operation, it actually reads the sql operation in the mysql-bin.003 binary log in master and performs these sql operations in slave. Therefore, the following conditions apply to the success of synchronization:

Master and slave have the same database table structure, and preferably database name (can be set to a different database name)

Master and slave have the same initial data records to ensure that the two data are consistent after the start of the synchronization operation.

Master must use bin-log binary logging (bin-log is also recommended for slave)

Slave is synchronized by reading sql records from the bin-log of master, so it is important to start reading from which log. (operation 7 below is to ensure that slave can read the same records as master and read the correct bin-log log files.)

Perform the following sql operation in slave

Mysql > CHANGE MASTER TO

-> MASTER_HOST=master_host_name

-> MASTER_USER=replication_user_name

-> MASTER_PASSWORD=replication_password

-> MASTER_LOG_FILE=recorded_log_file_name

-> MASTER_LOG_POS=recorded_log_position

Here, change the above redcorded_log_file_name to mysql-bin.003 and recorded_log_position to 73 (note that this is the key to the relationship).

What are the skills of mysql under linux and MySQL under win2k as replication:

Note that there is case sensitivity, but not in win2k. So it's best to use the mysqldump method to synchronize the two initializations, rather than using tar to directly copy the database table files. It is also good to use studio as database backup.

Note that the mysql configuration file under win2k is c:my.ini. You can set it using mysql administrator 0.9, a utility in the graphical interface.

If the mysql under win2k is master, be sure to pay attention to the results displayed in show master status.

When the mysql under win2k is slave, be careful not to set the synchronization time period too long.

Actual combat

Environment configuration

Operating system: linux

MySQL:5.14

Primary server An IP:192.168.1.2 (hypothetical)

IP:192.168.1.3 from server B (hypothetically)

Synchronous database: test (for testing, the initial data of the two servers are the same)

Primary server (master) settin

Create a new user for backup (you can also use root user directly):

GRANT FILE ON *. * TO IDENTIFIED BY '123456'

Edit the "/ etc/my.cnf" file (different servers may have different paths) and add the following to the file:

Server-id=1

# set server id, master and slave servers should be different

Log-bin=mysqllog

# enable binary change logging (that is, log all SQL commands that operate on data in binary format)

# where mysqllog is the name of the log file, and the file name of the log is mysqllog.n, where n is a 6-digit integer.

Binlog-do-db=test

# specify the database "test" for which binary change logging needs to be enabled

Binlog-ignore-db = mysql

# specify a database "mysql" that does not need to enable binary change logs

To restart the mysqld service, you can view the "enable binary change log" situation with the mysql command: SHOW MASTER STATUS;

Set from the server (slave)

Edit the "/ etc/my.cnf" file (different servers may have different paths) and add the following to the file:

Server-id=2

# set server id, master and slave servers should be different

Replicate-do-db=test

# specify the database "test" that needs to be synchronized from master

Replicate-ignore-db = mysql

# specify database "mysql" that does not need to be synchronized from master

# set the IP address, login user, password and port of the master server

Master-host=192.168.1.2

Master-user=backup

Master-password=123456

Master-port=3306

Master-connect-retry=60

# delay time of retry after failed to connect to master server

Slave-skip-errors=all

# skip all errors and continue to synchronize

Log-slave-updates

# enable log synchronization on secondary servers

Note: if a master.info file exists on the server (e.g. / var/lib/mysql/master.info), for the above configuration options to take effect, you must delete the file before restarting the mysqld service.

To restart the mysqld service, you can check the synchronization with the mysql command: SHOW SLAVE STATUS;

Relevant commands from the database:

Slave st; slave start; starts to stop from the database.

Show slave statusG; shows which master database binary log is being read from the slave library

After successfully completing the above configuration, add or delete data in the test library of master server A, and the corresponding changes can be seen immediately in the test library of slave server B. The synchronization between the two servers can be said to be completed in an instant.

Thank you for reading this article carefully. I hope the article "how to achieve MySQL database synchronization" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.