MySQL database configuration master-slave server for dual-machine hot standby

Transferred from: http://www.cnblogs.com/cchun/p/3712637.html

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 #进入MySQL控制台Create DatabaseOsyunweidb; #建立数据库osyunweidbInsert  intoMysql.User(Host,User, Password)Values('localhost','Osyunweiuser', Password ('123456')); #创建用户osyunweiuser # build MySQL master-slave database sync user osyunweidbbak password 123456 flushPrivileges; #刷新系统授权表 # Authorized user Osyunweidbbak only from 192.168.21.168 This IP accesses the master server 192.168.21. 169 the database above and only has database backup permissionsGrant ReplicationSlave 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主服务器进行操作, 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控制台  create  database     Osyunweidb;  #创建数据库  use   Osyunweidb #进入数据库source  / home/  - u osyunweidbbak - h 21.169  - p #测试在从服务器上登录到主服务器  

iv. Configuring the MySQL master server my.cnf file
vi/etc/my.cnf #编辑配置文件, add the following in the [Mysqld] section
server-id=1 #设置服务器id, 1 indicates the primary server, note: If the original configuration file already has this line, you can no longer add.
Log_bin=mysql-bin #启动MySQ二进制日志系统, note: If you already have this line in your original configuration file, you won't have to add it anymore.
Binlog-do-db=osyunweidb #需要同步的数据库名, if you have multiple databases, you can repeat this parameter, one row per database
Binlog-ignore-db=mysql #不同步mysql系统数据库
Service mysqld Restart #重启MySQL
mysql-u root-p #进入mysql控制台
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 : The value of mysql-bin.000019 and position:7131, which is 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] sectionServer-id=2   #The configuration file already has a row server-id=1, which modifies its value to 2, as from the databaseLog-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. replicate- 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 databaseReplicate-ignore-db=mysql#Out of sync MySQL system database: wq!#Save ExitService mysqld Restart#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 #进入MySQL控制台
slave stop; #停止slave同步进程
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; #执行同步语句
slave start; #开启slave同步进程
show SLAVE Status\g #查看slave同步信息, the following 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:
1 row in Set (0.00 sec)
Note check:
Slave_io_running:yes
Slave_sql_running:yes
The values for these two parameters are yes, which means the configuration was successful!
 

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

- - p  #进入主服务器MySQL控制台use   osyunweidb   #进入数据库 CREATETABLEint not nullprimarykeychar(());   #创建test表

2. Enter MySQL from the server

- - p  #进入MySQL控制台use   osyunweidb   #进入数据库show  tables;  #查看osyunweidb表结构, you 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 standby Example tutorial completed

MySQL database configuration master-slave server for dual-machine hot standby