MySQL database synchronization _ MySQL

MySQL database synchronization bitsCN.com

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.
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.
Next, we will explain the core 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.
Practice
Environment configuration
Operating system: linux
MySQL: 5.14.
Master server a ip: 192.168.1.2 (hypothesis)
Slave server B IP: 192.168.1.3 (hypothesis)
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@192.168.1.3 identified by '20140901 ';

Edit the "/etc/my. cnf" file (different server paths may be different) and add the following content to the file:
Server-id = 1
# Set the server id. the master/slave servers must be different.
Log-bin = mysqllog
# Enable binary change logs (that is, all SQL commands that operate on data are logged in binary format)
# Here, mysqllog is the name of the log file, and the log file name is mysqllog. n, where n is a 6-digit integer.
Binlog-do-db = test
# Specify the database "test" for which binary change logs need to be enabled"
Binlog-ignore-db = mysql
# Specify the database "mysql" that does not need to enable the binary change log"

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/slave servers must be different.
Replicate-do-db = test
# Specify the database "test" to be synchronized from the master"
Replicate-ignore-db = mysql
# Specify the database "mysql" that does not need to be synchronized from the master"

# Set the IP address, login user, password, and port of the master server
Master-host = 192.168.1.2
Master-user = backup
Masters-password = 123456
Master-port = 3306

Master-connect-retry = 60
# Retry delay after failed connection to the master server
Slave-skip-errors = all
# Skip all errors and continue synchronization
Log-slave-updates
# Enable log synchronization on the slave server
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.
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.

BitsCN.com