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

This article mainly introduces the mysql database synchronization example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

Synchronous introduction:

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.

Synchronization principle:

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. 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.

Synchronization 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

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.

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 with which log to read (the operation in article 7 below is to ensure that slave can keep 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.

Notice 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.

Simultaneous actual combat:

Environment:

Environment configuration

Operating system: CentOS release 5.3 (Final) 64bit

MySQL:5.14

Primary server An IP:10.224.194.239

IP:10.224.194.237 from server B

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 backup@10.224.194.237 IDENTIFIED BY 'pass'

GRANT REPLICATION SLAVE ON *. * TO backup@10.224.194.237 IDENTIFIED BY 'pass'

Edit the "/ etc/my.cnf" file (different servers may have different paths) and add the following: server-id=1 # set server id, master and slave servers want different log-bin=mysqllog # enable binary change log (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" binlog-ignore-db = mysql # where the binary change log needs to be enabled specify the database "mysql" that does not need to enable the binary change log

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 up server id Different replicate-do-db=test # specify the database "test" replicate-ignore-db = mysql # that needs to be synchronized from master # specify the database "mysql" that does not need to be synchronized from master # set the IP address, login user, password, Port master-host=10.224.194.239 master-user=backup master-password=pass master-port=3306 master-connect-retry=60 # delay time of retry after failed connection to master server slave-skip-errors=all # skip all errors continue synchronization log-slave-updates # enable log synchronization on secondary server

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

Verify:

Create a table named test01 on the primary / secondary server. It is best to set the primary key: after inserting a piece of data on the primary server, it will be synchronized to the secondary server. If it is not successful, check whether the log, number configuration item is correct.

Create table test01

(name_id varchar (10) not null

Primary key (name_id)

)

Insert into test01 (name_id) value (1)

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 "sample Analysis of mysql Database synchronization" shared by the editor will be helpful to you. 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.