MySQL Master-slave backup

MySQLdual-Machine hot standby

Environment description

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4C/C1/wKioL1RE0zbAm9B1AAJWSS3H_Mc796.jpg "title=" 1.jpg " alt= "Wkiol1re0zbam9b1aajwss3h_mc796.jpg"/>

Msql Primary and standby structure

1, Master:

Mysql master node, master receives a request from the IO process from slave, reads the log information after the specified location of the log according to the requested information through the IO process responsible for the replication, and returns the IO process to the slave. In addition to the information contained in the log, the returned information includes the name of the Bin-log file returned to the master side and the location of the Bin-log;

2 , Slave:

Slave node, after the slave IO process receives the information, the received log content is added to the end of the Relay-log file on the slave side, and the read to the master side of the The file name and location of the Bin-log are recorded in the Master-info file so that the location of the masterbin-log can be clearly told at the next read.

1 , MySQL master server

System: CentOS 6.2

IP : 192.168.1.251

Host Name: Mysqlmaster

MySQL version: mysql-5.5.22

2 , MySQL slave server

System: CentOS 6.2

IP : 192.168.1.252

Host Name: Mysqlslave

MySQL version: mysql-5.5.22

First, the configurationMySQLmaster-Slave server

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4C/C1/wKioL1RE00Tie8-dAAWWTrGltR0552.jpg "title=" 2.png " alt= "Wkiol1re00tie8-daawwtrgltr0552.jpg"/>

Modify the IP of both servers and then use the RPM package to install the MySQL database.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4C/C1/wKioL1RE07eTVL4PAAIyNUeA_CM597.jpg "title=" 3.png " alt= "Wkiol1re07etvl4paaiynuea_cm597.jpg"/>

Using the Mysql–u root–p login password is empty by default,

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4C/C1/wKioL1RE08PRc1jpAABpoOyKMWA691.jpg "title=" 4.png " alt= "Wkiol1re08prc1jpaabpooykmwa691.jpg"/>

Establish database smiledb;

#create database smiledb;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4C/C0/wKiom1RE04WC2GgFAAEg0g2EbQw606.jpg "title=" 5.png " alt= "Wkiom1re04wc2ggfaaeg0g2ebqw606.jpg"/>

Authorized users rsync can only from 192.168.1.252 this IP

Access the primary server 192.168.1.251 the database above and only have database backup permissions.

# grant Replication Slave on * * to ' rsync ' @ ' 192.168.1.252 ' identified by ' 123456 ' with GRANT option;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4C/C1/wKioL1RE0-uCR7lpAACReTE_GE0845.jpg "title=" 6.png " alt= "Wkiol1re0-ucr7lpaacrete_ge0845.jpg"/>

Refresh the system authorization form,

#flush privileges;

Note: Before exporting, you can go to the MySQL console and execute the following command

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4C/C0/wKiom1RE066SmDtZAACDnxQCKbw818.jpg "title=" 7.png " alt= "Wkiom1re066smdtzaacdnxqckbw818.jpg"/>

the database read-only lock command prevents data from being written when the database is exported,

#flush tables Withread lock;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4C/C0/wKiom1RE07-DT-nmAAB4lsRb0Is394.jpg "title=" 8.png " alt= "Wkiom1re07-dt-nmaab4lsrb0is394.jpg"/>

# in MySQL master service

To export the database smiledb to/root/smile.sql

#mysql-u root-p smiledb >/root/smile.sql

Upload the backup smile.sql to the slave server (upload everyone I will not show)

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4C/C0/wKiom1RE09yy55O1AABaIqGCfSI751.jpg "title=" 9.png " alt= "Wkiom1re09yy55o1aabaiqgcfsi751.jpg"/>

Create the Smiledb library from the server first;

#create database smiledb;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/4C/C0/wKiom1RE1GeCRjBHAABQfNKuQLQ235.jpg "title=" 8 complement. png "alt=" Wkiom1re1gecrjbhaabqfnkuqlq235.jpg "/>

Using use SMILDB; entering the library

#use Smiledb;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4C/C1/wKioL1RE1MzRhb0kAAGC6RP-Glg050.jpg "title=" 10.png "alt=" Wkiol1re1mzrhb0kaagc6rp-glg050.jpg "/>

Import the backup files from the database,

#source/root/smile.sql

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4C/C0/wKiom1RE1KSCeKIgAACBJqGDKxk307.jpg "title=" 11.png "Style=" Float:none; "alt=" wkiom1re1kscekigaacbjqgdkxk307.jpg "/>

The test logs on from the server to the primary server,

Second, the configurationMySQL(Master)--Server'smy.cnffile

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4C/C1/wKioL1RE1O6zf7qsAAKTM5zGrsQ981.jpg "title=" 12.png "Style=" Float:none; "alt=" wkiol1re1o6zf7qsaaktm5zgrsq981.jpg "/>

, modify the My.cnf file, (described above in very detailed)

Modify the primary server /usr/share/mysql/my-my-large.cnf to MY.CNF and move to the/etc/directory

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4C/C0/wKiom1RE1KXR3cX7AACpNNM-LMw251.jpg "title=" 13.png "Style=" Float:none; "alt=" wkiom1re1kxr3cx7aacpnnm-lmw251.jpg "/>

Then restart MySQL

#service MySQL Restart

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4C/C1/wKioL1RE1O_xH2syAAFTuyjByHw776.jpg "title=" 14.png "Style=" Float:none; "alt=" wkiol1re1o_xh2syaaftuyjbyhw776.jpg "/>

Enter the MySQL console again, using show Master status; The library must be locked or there will be a change.

View the binary log currently in use by the master database and the current execution binary log location;

Third, the configurationMySQL(from)--Server'smy.cnffile

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4C/C0/wKiom1RE1KWTk323AAJujk3M5w8485.jpg "title=" 15.png "Style=" Float:none; "alt=" wkiom1re1kwtk323aajujk3m5w8485.jpg "/>

, modify the My.cnf file, (described above in very detailed)

will be modified from server /usr/share/mysql/my-my-large.cnf to MY.CNF and moved to/etc/directory

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4C/C1/wKioL1RE1O_gf2ZoAAC0zKgdoZA027.jpg "title=" 16.png "Style=" Float:none; "alt=" wkiol1re1o_gf2zoaac0zkgdoza027.jpg "/>

Then restart MySQL

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4C/C1/wKioL1RE1O-wMdGdAACB_p-OIjk661.jpg "title=" 17.png "Style=" Float:none; "alt=" wkiol1re1o-wmdgdaacb_p-oijk661.jpg "/>

Go to MySQL console stop slave sync process # slave stop

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4C/C0/wKiom1RE1KWAWPCpAAE8wMlvaUA024.jpg "title=" 18.png "Style=" Float:none; "alt=" wkiom1re1kwawpcpaae8wmlvaua024.jpg "/>

Executes the synchronization statement;

#change Master to master_host= ' 192.168.1.210 ', master_user= ' rsync ', master_password= ' 123456 ', master_port=5656, Master_log_file= ' mysql-bin.000001 ', master_log_pos=107;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4C/C0/wKiom1RE1cGTuTNrAAB5ph5xXWE070.jpg "title=" 19.png "alt=" Wkiom1re1cgtutnraab5ph5xxwe070.jpg "/>

Open slave synchronization information;

#slave start

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4C/C1/wKioL1RE1PDi7PHEAAB2S37RauE160.jpg "title=" 20.png "Style=" Float:none; "alt=" wkiol1re1pdi7pheaab2s37raue160.jpg "/>

Do not forget to release the main server library lock;

#unlock tables;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4C/C0/wKiom1RE1KixJghaAAJoTXQN7bs034.jpg "title=" 21.png "Style=" Float:none; "alt=" wkiom1re1kixjghaaajotxqn7bs034.jpg "/>

Finally use show SLAVE status\g to view the synchronization information;

Note check:

Slave_io_running:yes Slave_sql_running:yes

The values for these two parameters are yes, which means the configuration was successful!

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4C/C1/wKioL1RE1PLSul0oAAE90I628pA967.jpg "title=" 22.png "Style=" Float:none; "alt=" wkiol1re1plsul0oaae90i628pa967.jpg "/>

Enter the master server to create the test table to see if the server is synchronized.

# Use Smiledb, #create table test (ID int not NULL primary Key,namechar (20));

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4C/C1/wKioL1RE1PKwogm7AAQW64Y8lUs654.jpg "title=" 23.png "Style=" Float:none; "alt=" wkiol1re1pkwogm7aaqw64y8lus654.jpg "/>

Show tables; # View the SMILEDB table structure and see a new table test that indicates that the database synchronization was successful

At this point, the MySQL database configuration master-slave server to implement a two-machine hot spare instance tutorial completed.

Note: You need to synchronize those databases remember to add them in my.cnf.

This article is from the Linux OPS blog, so be sure to keep this source http://linuxdarker.blog.51cto.com/8340258/1565975

MySQL Master-slave backup