Mysql database sync _ MySQL

Mysql database synchronization details bitsCN.com

Synchronization introduction:

MySQL data synchronization, in the official MySQL website documentation, is called Replication literally meaning, is synchronized. In fact, MySQL synchronization does not use the word "sync", but instead uses replication again, which accurately shows the essence of MySQL database operations, or re-perform the same operation to keep the data of the master database server and the slave server slave consistent. Replication means repeated replication.

Synchronization principle:

To implement replication, MySQL must open the bin-log entry and enable the binary MySQL logging option. The binlog binary log of MySQL records all SQL operations that affect the content stored in the database table, such as insert, update, and delete operations, but not select operations. Therefore, we can use binary logs to restore lost data within a certain period of time to the database (if the log items recorded in binary logs include all the data in the database table, then, you can recover all the data in the local database ). If this binary log is used for remote database recovery, it is replication. This is why replication is used instead of sync. This is why bin-log = is set.

During the synchronization process, the most important synchronization reference object is to synchronize the binary log file from that record.

Synchronization process:

First, you should have two or more MySQL database servers, preferably at least 3.3 (of course, the two servers are not necessarily two machines, two MySQL services can be installed on one machine. at the same time, if you are very familiar with the MySQL replication principle, you can even synchronize data between two different database databases of a MySQL service, see if necessary) Note: These two servers are generally set to one master server, or the source server, master mysql server, and the other or multiple servers are replication slave synchronous slave servers. One slave is the same as multiple slave settings, so that you can make similar database clusters.

Set the accessible MySQL account. The operation is in English.

MySQL accounts are generally set to restrict IP access to ensure security.

MySQL accounts are generally set to the same account on the master and slave, and are remotely accessible.

Note: If you are using a Linux/Unix operating system, check whether firewall limits MySQL remote access. If yes, it is best to open the remote access port and set access IP address restrictions.

Because MySQL account and password must be stored in plain text in my. cnf, please ensure that my. cnf is not accessible to other users (it seems that passwd should be stored with the password after instructions to MySQL ).

Step 3: Set the databases to be synchronized between the two servers to the same database.

Here are some tips, including instructions on mysql SQL commands.

One method is described in the English description. first, lock the database read/write function (in fact, it is best to stop the mysqld service and then back up the database) and then use tar to back up the database directory, go to the same data directory of the slave server.

Method 2: Use a utility such as mysql studio to directly use the backup database tool of mysql studio to synchronize the database

Both methods have many data records in the master database. the master database and slave have the same database and data records in the above two methods. The third method is suitable for creating a database, especially for master and slave in my. when the replication relationship (but database synchronization is not specified) has been set for cnf: Use the SQL statement of mysql to 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, so that a synchronized table can be created. Load data from master is to import data from the master database to the slave data table, provided that the master has used the bin-log parameter since the start of installation and running, and stores binary logs

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

Tip: If you use mysql of win2k, you can download the official mysql administrator utility from mysql.com and directly configure master and slave in mysql administrator, you can also configure query-cache.

Add

[Mysqld]

Log-bin =

Server-id = 1

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

Modify my. cnf in slave

[Mysqld]

Server-id = 2 # if multiple slave instances exist, replace them with unique ids. in mysql 4.1, this has been canceled.

Master-host = 10.10.10.22

Master-user = backup # synchronize user accounts

Masters-password = 1234

Master-port = 3306

Master-connect-retry = 60 # preset retry interval 60 seconds

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

Bin-log =

Check the Configuration status of master and slave. use the show master status and show slave status commands to view the Configuration status of master and slave. Two state variables 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 a mysql-bin.003 and position is 73

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

As we have said before, binary logs record SQL operation statements that change all data records of a database, such as insert, update, and delete. this is precisely because, when slave synchronizes mysql database operations, in fact, it is to read the SQL operations in the mysql-bin.003 binary log in the master, and execute these SQL operations in the slave, so the synchronization is successful with the following conditions:

The master and slave have the same database table structure, and the database name should be the same (you can set it to a different database name)

The master and slave have the same initial data records to ensure that the data is consistent after the synchronization operation starts.

The master must use the bin-log binary log record (we recommend that slave also use the bin-log)

Slave reads SQL records from the bin-log of the master node for synchronization. Therefore, it is very important to start reading from which log (the following 7th operations, it is to ensure that slave can read the same records as the master and read the correct bin-log file)

Perform the following SQL operations on slave:

Mysql> CHANGE MASTER

-> 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 the mysql-bin.003 and recorded_log_position to 73 (note that this is the key to the relationship ).

What are the skills of mysql in linux and MySQL in win2k for replication:

Note that Linux is case sensitive, but win2k is not. Therefore, it is best to use the mysqldump method to initialize and synchronize the two databases, instead of using tar to directly copy database table files. Using mysql studio for database backup is also good

Note that the mysql configuration file in win2k is c:/my. ini. You can use mysql administrator 0.9 as a practical tool on the graphic interface.

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

When mysql in win2k is slave, be sure not to set the synchronization period too long.

Synchronization practice:

Environment:

Environment configuration

Operating system: CentOS release 5.3 (Final) 64bit

MySQL: 5.14.

Master server a ip: 10.224.194.239

Slave server B IP: 10.224.194.237

Synchronized database: test (used for testing, the initial data of the two servers is consistent)

Master settings

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

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 "/etc/my. add the following content to the cnf File (different server paths may vary): server-id = 1 # set server id, the master and slave servers must have different log-bin = mysqllog # enable the binary change log (that is, all SQL commands that operate on data are logged in binary format) # mysqllog is the name of the log file, the log file name is mysqllog. n, where n is a 6-digit integer. Binlog-do-db = test # specify the database that needs to enable binary change logs "test" binlog-ignore-db = mysql # specify the database that does not need to enable binary change logs "mysql"

To restart the mysqld service, run the mysql command: show master status; to view "enable binary Change Log ".

Slave server (slave) settings

Edit the "/etc/my. cnf" file (different server paths may be different) and add the following content to the file:

Server-id = 2 # set the server id, the master and slave servers must have different replicate-do-db = test # specify the database to be synchronized from the master database "test" replicate-ignore-db = mysql # specify the database not to be synchronized from the master "mysql" # set the IP address, login user, password, and port master-host = 10.224.194.239 master-user = backup master-password = pass master-port = 3306 master-connect- retry = 60 # delay time for retry after master server connection failure slave-skip-errors = all # skip all errors and continue to execute synchronization job log-slave-updates # enable log on slave server synchronization

Note: If the master.info File (for example,/var/lib/mysql/master.info) exists on the slave server, you must delete this file before restarting the mysqld service.

Restart the mysqld service. run the mysql command: show slave status; to view the synchronization STATUS.

Commands related to the slave database:

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

Show slave statusG; displays the binary log of the primary database that is being read from the database.

Verification:

Create a table named test01 on the primary/secondary server. it is recommended that you set the primary key to insert a data entry on the primary server, which will be synchronized to the secondary server. if no data entry is successfully inserted, check whether the log and number configuration items are correct

Create table test01

(Name_id varchar (10) not null,

Primary key (name_id)

)

Insert into test01 (name_id) value (1 );

After completing the preceding configuration, add or delete data in the test database of master server A. the corresponding changes can be seen immediately in the test database of master server B. Synchronization between the two servers can be achieved instantly.

This article is from "small steps and continuous steps !" Blog

BitsCN.com