MySQL database master-slave synchronization installation and configuration Summary

The master-slave synchronization of MySQL is a mature architecture with the following advantages: ① the query can be performed on the slave server (that is, the READ function we often call) to reduce the pressure on the master server; ② back up data from the master server to avoid affecting the services of the master server during the backup process; ③ when the master server encounters problems, you can switch to the slave server. Therefore, this solution is often used in project deployment and implementation. + database directory and other my. cnf configuration file/etc/my. cnfmysql database location datadir =/var/lib/mysql master database: 192.168.2.119 slave Database: 192.168.2.220 Operating System: RHEL5.x 32-bit server type: Virtual Machine + mysql5.0.77 installation: ① After the yum service of linux is configured, install mysql directly using yum-y. The following command is provided to install php/mysql: yum-y install httpd php mysql-server php-mysql ② start MySQLservice mysqld start (restart | stop) I. Set master database 1. Modify master database my. cnf is mainly used to set a different id and logbin (# This depends on the specific environment, and huge can be used for high-pressure conversion. cnf) [root @ Localhost etc] # vi/etc/my. cnf # Remember that this part must be configured after [mysqld]; otherwise, the slave node cannot be found, for the meaning of each configuration item, see the document [mysqld] log-bin = mysql-binserver-id = 1binlog-ignore-db = information_schemabinlog-ignore-db = clusterbinlog-ignore-db = mysql2, start the master database to take effect [root @ localhost etc] service mysqld restart3. log on to the master database [root @ localhost etc] mysql-u root-p4, and grant the slave database permission account, allow users to read logs from the master database mysql> grant all privileges on *. * to 'username '@' % 'identified by 'Password'; 5. Check whether the user is created successfully, Host from mysql. user; 6. lock the master database table mysql> flush tables with read lock; 7. display the File and Position records of the master database. mysql> show master status will be used for slave database settings; + metric + ---------- + -------------- + ---------------- + | File | Position | Binlog_Do_DB | usage | + usage + ---------- + ------------ + usage + | mysql-bin.000001 98 | + usage + ---------- + ------------------ + ------------------ + 1 Row in set (0.00 sec) # Note: If the execution of this step is always Empty set (0.00 sec), it indicates that the previous my. cnf is not configured. 8. log on to 220 on another terminal and package the data for the master database for Migration (if the yum installation you are using does not make any changes to the default database, you do not need to copy the data) the purpose is to ensure that the mysql databases of the two servers are consistent. Here, you can package your own tar or use the mysqldump command to back up and restore the database. 2. Set slave database 1. Transfer the master database package and unpack it # log on to the slave database and restore it from the database backed up in the previous step to the 220 server node. 2. unlock the master database table on the 119 node (corresponding to the operation for locking the master database table in step 1 of the master database at the first point) mysql> unlock tables; 3. Modify slave database my on node 220. cnf (same location) [root @ localhost etc] vi my. cnf # Remember that this part must be configured after [mysqld]; otherwise, the slave node cannot be found, for more information about each configuration item, see [mysqld] log-bin = mysql-binserver-id = 2binlog-ignore-db = information_schemabinlog-ignore-db = clusterbinlog-ignore-db = mysqlreplicate-do-db = testreplicate-ignore -db = mysqllog-slave-updatesslave-skip-errors = allslave-net-timeout = 60 master-host = 192.16 8.2.119master-user = rootmaster-password = pfingo4. Verify the connection to the master database [root @ localhost etc] mysql-h 192.168.2.119-u username-p 5 on the master node 220, And the slave database on the master node 220. set synchronization # Set the connection MASTER MASTER_LOG_FILE as the File of the MASTER database, MASTER_LOG_POS is the Position of the master database # note that in the second command statement below, master_log_file = 'mysql-bin.000001 'and master_log_pos = 98; corresponding to the show master status previously executed in the master database; result: mysql> slave stop; mysql> change master to master_host = '2017. 168.2.119 ', master_user = 'root', master_password = 'pfi Ngo ', master_log_file = 'mysql-bin.000001', master_log_pos = 98; mysql> slave start; 6. start slave Database Service mysql> slave start; 7. perform the test and CREATE a mysql> create table 'mytest' ('id' INT (5) unsigned not null AUTO_INCREMENT, 'username' VARCHAR (20) not null, 'Password' CHAR (32) not null, 'last _ Update' datetime not null, 'number' FLOAT (10) not null, 'content' text not null, primary key ('id') ENGINE = MYISAM; the results are immediately displayed in the table, and the master-slave synchronization is successful. To further verify, enter show slave status/G on the slave database; mysql> show slave status/G; Slave_IO_Running: yes (the network is normal); Slave_ SQL _Running: Yes (the table structure is normal) further verifies the correctness of the above process.