MySQL Master-slave configuration

Tutorial Start:
First, install MySQL

Description: install MySQL 5.5.22 on two MySQL servers 192.168.21.169 and 192.168.21.168, respectively

Second, configure mysql master server (192.168.21.169)
mysql  -uroot  -p     # enter MySQL console
create database osyunweidb;   # osyunweidb
INSERT INTO Mysql.user (Host,user,password) VALUES (' localhost ', ' osyunweiuser ' , password (' 123456 '));    #创建用户 osyunweiuser

#建立mysql master-slave database synchronization user osyunweidbbak password 123456

Flush privileges; #刷新系统授权表

#授权用户 Osyunweidbbak can only from 192.168.21.168 this IP Access master server 192.168.21.169 database above and only has database backup permissions
grant replication slave  on * * to ' osyunweidbbak ' @ ' 192.168.21.168 ' identified by ' 123456 ' with Grant option; 

Third, the MySQL master server 192.168.21.169 in the database osyunweidb imported to MySQL from the server 192.168.21.168
1. Export Database osyunweidb

Mysqldump-u root-p osyunweidb >/home/osyunweidbbak.sql #在mysql master server, export database osyunweidb to /home/ Osyunweidbbak.sql

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

Flush tables with read lock; #数据库只读锁定命令 to prevent data from being written when the database is exported

Unlock tables; #解除锁定

2. Import database to MySQL from server

Mysql-u root-p #进入从服务器mysql console

Create Database Osyunweidb; #创建数据库

Use Osyunweidb #进入数据库

Source/home/osyunweidbbak.sql #导入备份文件到数据库

mysql-u osyunweidbbak-h 192.168.21.169-p #test to log on to the primary server from the server
iv. Configuring the MySQL master server my.cnf file
VI/ETC/MY.CNF #Edit the configuration file and add the following in the [mysqld] section
server-id=1 # Set the server ID to 1 for the primary server, note: If the original configuration file already has this line, it is no longer added.
Log_bin=mysql-bin # start the mysq binary logging system, note: If the original configuration file already has this line, there is no need to add.
binlog-do-db=osyunweidb # The name of the database that needs to be synchronized, and if there are multiple databases, repeat this parameter, one row per database
binlog-ignore-db=mysql # Out of sync mysql system database
Service mysqld Restart # Restart MySQL
Mysql-u root-p #进入mysql console
show master status;to view the primary server, a similar message appears
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000019 | 7131 | Osyunweidb | MySQL |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)
Note: The value of file is remembered here: mysql-bin.000019 and The value of position: 7131 , which will be used later.
V. Configure MySQL my.cnf files from the server
vi/etc/my.cnf # Edit the configuration file and add the following in the [mysqld] section
server-id=2 #The configuration file already has a row server-id=1, which modifies its value to 2, as from the database
Log-bin=mysql-bin#启动mysq binary log system, note: If this line is already in the original configuration file, you will not have to add it again.
replicate-do-db=osyunweidb #需要同步的数据库名, if you have multiple databases, you can repeat this parameter, one row per database
Replicate-ignore-db=mysql #Out of sync mysql system database
: wq! #保存退出
Service mysqld Restart #重启MySQL
Note:after MySQL 5.1.7, it is not supported to write the master configuration attribute to the my.cnf configuration file, just write the synchronized database and the database to be ignored.
mysql-u Root-p #go to MySQL console
slave stop; #Stop slave synchronization process
Change master to master_host= '192.168.21.169', master_user= 'osyunweidbbak', master_password= '123456', Master_log_    file=' mysql-bin.000019 ' , master_log_pos=7131; #executing a synchronous statement
slave start; #turn on the slave synchronization process
SHOW SLAVE Status\g # to view slave synchronization information, the following content appears
*************************** 1. Row ***************************
slave_io_state:waiting for Master to send event
master_host:192.168.21.169
Master_user:osyunweidbbak
master_port:3306
connect_retry:60
master_log_file:mysql-bin.000019
read_master_log_pos:7131
relay_log_file:mysqlslave-relay-bin.000002
relay_log_pos:253
relay_master_log_file:mysql-bin.000019
Slave_io_running:yes
Slave_sql_running:yes
Replicate_do_db:osyunweidb
Replicate_ignore_db:mysql
replicate_do_table:
        replicate_ignore_table:
Span style= "font-size:medium;" >1 Row in Set (0.00 sec)
Note view:
slave_ Io_running:yes
slave_sql_running:yes
yes, which means the configuration was successful!
 

Six, test MySQL master server dual-Machine hot standby is successful
1. Go to mysql master server

Mysql-u root-p #进入主服务器mysql console

Use Osyunweidb #进入数据库

CREATE TABLE Test (ID int not NULL primary key,name char (20)); # create test table
2. Enter MySQL from the server

Mysql-u root-p #进入mysql console

Use Osyunweidb #进入数据库

Show tables; #查看 the OSYUNWEIDB table structure, you see a new table test that indicates that the database synchronization was successful

At this point, theMySQL database configuration master-slave server to implement a two-machine hot standby Example tutorial completed

MySQL Master-slave configuration