Brief introduction to MySQL database replication method _ MySQL

This article mainly introduces the MySQL database replication method, and uses the common mysqldump tool. if you need it, you can refer to MySQL replication, which means to copy data from a node (master) copy to another node (slaves). of course, the method varies depending on the application architecture. the replication between MySQL servers uses the binary log mechanism. updates and changes to the master node are recorded in logs as events. The information in logs is recorded in different formats as they change. slaves is configured to read logs from the master and execute events in binary logs to the slave local database. once the master node starts the binary log function, all statement operations will be recorded, and each slave will receive a copy of the entire log content. the responsibility of slave is to determine which statement in the log needs to be executed, but we cannot configure the master to only record some specific events. if not specified separately, all the events in the binary log of the master server are executed on the slave. if necessary, you can configure slave to only apply events from a specific database or table.

According to the MySQL replication mechanism, the replication process generally has three threads in the master-slave architecture to complete the replication. on the master server, dump thread, i/O thread on the slave Server and SQL thread on the slave server. if you want to use multiple threads, you need to install a patch. this works based on the one-master-one-slave architecture replication, the master server writes the operation to the binary log. the dump thread on the master server sends the log to the I/O thread on the slave server, and stores the log as a relay, the SQL thread writes data to the slave server based on the relay log. the replication architecture is configured below,

Copy commands for different databases on the same host: note that commands run in Terminal instead of MySQL command lines.

 mysqldump Portal_DEV -u root -ppassword1$ --add-drop-table | mysql Portal_Optimize -u root -ppassword1$

During the replication process, the following error occurs:

ERROR 1153 (08S01) at line 1160: Got a packet bigger than 'max_allowed_packet' bytes mysqldump: Got errno 32 on write

Max_allowed_packet in my. cnf needs to be zoomed in. the default value of my local machine is 1 M, which is zoomed in to 32 M. Restart MySQL.

OK. the above solution applies to databases with small data volumes. Below is a large capacity (more than 1 GB.

 mysqldump -uroot -ppassword --quick Portal_DEV | gzip > Portal_DEV.contents.gz

Export the portal_devdata library to a portal_dev.contents.gz compressed file.

gunzip < Portal_DEV.contents.gz | mysql -uroot -ppassword Portal_Optimize

Run the preceding command to import Portal_DEV to the Portal_Optimize database.

Perfect, it's done !!!

The above is a brief description of the MySQL database replication method _ MySQL content, for more information, please follow the PHP Chinese network (www.php1.cn )!