This article is mainly a record, referring to the operation process of many technical articles on the Internet. I seem to have encountered some problems in using it myself, but in order to avoid forgetting it in the future, I decided to review this process. The article was operated in an environment where the Junge LNMP one-click package was installed on two American VPSs. MySQL Database master-slave synchronization has many advantages. For a superficial person like me, the most useful one is as a backup.

Because the two hosts I used in this example have already installed the LNMP one-click package, MySQL is already installed, and PhpMyadmin can manage the database, so the installation process is skipped. Some subsequent operations are also directly performed in PhpMyadmin. Riga.
First create a database on the main server and add users. I operate it directly in PhpMyadmin for visualization!
mysql_01
As shown above, I added a database named zhaorong. Then add the user, I also set the user name to zhaorong, and give him all permissions to the database zhaorong.
mysql_02
Note that the database name and user name set above can be set arbitrarily. It is convenient to remember that I set them to the same directly.
Next, we create a slave synchronization user for the database zhaorong on the main server and set the password.
mysql_03
The above figure creates a user zhaorong_c and specifies that it can only be accessed from the slave server IP. Note that the following permissions only have backup permissions.
mysql_04
Next, we export the database in the master server to the slave server, and log in to the master server via ssh to execute.
mysqldump -uroot -p**** –default-character-set=utf8 –opt -Q -R –skip-lock-tables zhaorong > /home/zr.sql
The above *** is MySQL Database root password (skip the first two -).
Pass the backup database to the slave server, where I also put it in the home directory.
scp /home/zr.sql [email protected]*:/home
Then we go to the slave server settings.

First configure the my.cnf file of the main server. The my.cnf of lnmp is in /etc/my.cnf
vi /etc/my.cnf     <x2 > #Edit
server-id=1     #This line itself has
log-bin=mysql-bin    #This line It has
binlog-do-db=zhaorong    #Join the database that needs to be synchronized
binlog-ignore-db=mysql    #Not synchronized MySQL system database
Save and exit.
service mysql restart   #Restart the database
mysql -uroot -p****     #Enter the MySQL database console
mysql> show variables like 'server_id';     #Check whether the server-id value is 1
mysql> show master status;     # Check the main server information
I still use screenshots to express the above two lines:
mysql_07
Record mysql-bin.000006 and 107 in the above picture for later use.
Then we configure the my.cnf file of the slave server.
vi /etc/my.cnf    #Same, edit the file
server-id=2    #The default is 1 to 2
log-bin=mysql-bin    #This line itself contains
replicate-do-db=zhaorong    #The database that needs to be synchronized
replicate-ignore-db=mysql    #Asynchronous system database
read_only     #Set read-only permission
Save and exit.
service mysql restart   #Restart the database
mysql -uroot -p****     #Enter the MySQL database console
mysql> show variables like 'server_id';     #Check that server-id is 2
mysql>slave stop;     #Stop slave Server synchronization process
mysql>change master to master_host='209.148.95.*',master_user='zhaorong_c',master_password='***',master_log_file='mysql-bin.000006′,master_log_pos=107;< x2>     #This line is relatively long. Note that mysql-bin.000006 and 107 are the previously recorded
mysql>slave start;  <x2 >  #Start the synchronization process
mysql>SHOW SLAVE STATUSG      #View the slave server synchronization information
mysql_08
As shown above, see It's OK when both lines in my red box are Yes.
After that, we can test it by creating a table on the master server, and then log in to the slave server database to see whether the table has been created synchronously.

Tags: MySQL, MySQL master-slave synchronization, MySQL synchronization, Database synchronization

window._bd_share_config={"common":{"bdSnsKey":{},"bdText":"","bdMini":"2","bdMiniList":["mshare","kaixin001","tsina"," tsohu","tqq","renren","qzone","weixin","bdysc","bdxc","tqf","tieba","douban","bdhome","sqq","thx" ],"bdPic":"","bdStyle":"0","bdSize":"24"},"slide":{"type":"slide","bdImg":"2","bdPos" :"left","bdTop":"100"},"selectShare":{"bdContainerClass":null,"bdSelectMiniList":["tsina","tqq","tsohu","qzone","renren", "weixin","mshare"]}};with(document)0[(getElementsByTagName('head')[0]||body).appendChild(createElement('script')).src='http://bdimg .share.baidu.com/static/api/js/share.js?v=89860593.js?cdnversion='+~(-new Date()/36e5)];
postid
21808

Leave a Reply