MySQL Master-slave replication

MySQL Master-slave replication

MySQL master-slave replication uses:

• Real-time disaster preparedness for failover

• Read and write separation, provide query service

• Backup to avoid impacting your business

Prerequisites for Master-Slave deployment:

Master-slave MySQL version consistent

• Main Library Open Binlog log (set log-bin parameter)

• Master-Slave Server-id different

• Connect to the main library from the library server

Master-Slave replication principle:

The Bin_log log function is turned on on the main SQL. It records SQL data information, is stored in a binary way in the disk, from the server through the I/O thread request to read the primary server's Bin_log binary log, and the resulting log information is written to the relay log (trunk log) file; Read from the Library's SQL thread relay Log file, parse into a specific SQL statement operation, the final implementation of data and unified, complete master-slave replication.

Environment Introduction:

function TD valign= "Top" colspan= "1" rowspan= "1" > /table>

HostName	OS	IP
master	centos6.5	192.168.100.150	as MySQL master server
salve	cen tos6.5	192.168.100.151	as MySQL slave server
ftp	centos6.5	192.168.100.100

1: Master-slave installation of MySQL:

[Email protected] ~]# yum-y install mysql-server [[email protected] ~]# yum-y install Msyql-server

2: Modify master-slave configuration file to support Bin_log logging

[[email protected] ~]# vi/etc/my.cnf 7 log-bin=mysql-bin # #支持bin-log logging, bin-log log file name starts with Mysql-bin 8 server-id=150 # #服务的唯一标识符号, the default is 1, here convenient memory, I used the last paragraph of IP [[email protected] ~]# vi/etc/my.cnf 7 server-id=151[[email protected] ~]#/etc /init.d/mysqld start # #重启服务 [[email protected] ~]#/etc/init.d/mysqld start

3: Grant permissions from replication on the primary database:

Login Server Authorization

[[email protected] ~]# mysqladmin-uroot password 123123[[email protected] ~]# mysql-uroot-p123123mysql> Grant Replic ation slave on * * to ' slave ' @ "192.168.100.%" identified by ' 123123 ';    Query OK, 0 rows Affected (0.00 sec) mysql> flush privileges; # #刷新权限Query OK, 0 rows Affected (0.00 sec) mysql>

To view the Bin-log log file information for the primary service:

you need to record the contents of the file and position two columns: whichever is found.

Mysql> Show Master status;+------------------+----------+--------------+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+|      mysql-bin.000003 |              476 |                  | |+------------------+----------+--------------+------------------+1 row in Set (0.00 sec)

4: Modify your master's database from the server

Log in to Database

[Email protected] ~]# mysqladmin-uroot password 123123[[email protected] ~]# mysql-uroot-p123123

Set up information to read Master Bin-log from the server

Mysql> Change Master to-master_host= ' 192.168.100.150 ', # #master的ip-master_user= ' slave ', # #授权允许复制的用户名-master_password= ' 123123 ', # #授权允许复制密码, master_log_file= ' mysql-bin.000003 ', # #bi     N-log file name, previous step in the information found on master, and master_log_pos=476; # #偏移量, information found on master query OK, 0 rows affected (0.07 sec)

Start slave

mysql> start slave; Query OK, 0 rows Affected (0.00 sec)

Insert card Slave Status:

# #查到的状态这两个为yes, no error below is normal Slave_io_running:yes Slave_sql_running:yes

mysql> show slave status\g;*************************** 1. row ***************                slave_io _state: waiting for master to send event                   master_host: 192.168.100.150                    master_user: slave                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000003           read_master_log_pos: 706                Relay_Log_File: mysqld-relay-bin.000002                 Relay_Log_Pos: 481         relay_master_log_file: mysql-bin.000003              Slave_IO_Running: Yes             slave_sql_running: yes               replicate_do_db:           replicate_ignore_db:             Replicate_Do_Table:         replicate_ignore_table:       replicate_wild_ do_table:    replicate_wild_ignore_table:                     Last_Errno: 0                    Last_Error:                   Skip_Counter: 0           Exec_Master_Log_Pos: 706               Relay_Log_Space: 637               Until_Condition: None                Until_Log_File:                  Until_Log_Pos: 0            master_ssl_allowed: no            master_ssl_ca_file:            master_ssl_ca _path:               master_ssl_ cert:             master_ssl_cipher:                 master_ssl_key:          seconds_behind_master: 0master_ssl_verify_server_ cert: no                 last_io_errno: 0                 Last_IO_Error:                 last_sql_errno:&nbSp;0               last_sql_error:  1 row in set  (0.00 sec) error: no query specifiedmysql>

5: Test:

To create a new library on the primary database, view the library

mysql> mysql> show databases;+--------------------+| database            |+--------------------+| information_schema | |  mysql              | |  test               |+-------- ------------+3 rows in set  (0.00 sec) mysql> create database test_ Databases query ok, 1 row affected  (0.00 sec) mysql> show databases;+--------- -----------+| database           |+--------------- -----+| information_schema | |  mysql              | |  test               | |  test_databases     |+--------------------+4 rows in set  (0.00  Sec

To view the library from the database:

Mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Test | | Test_databases |+--------------------+4 rows in Set (0.00 sec)

(You can see the new library on the main database, copy it over)

The delete operation can also be synchronized:

Main:

mysql> drop Database test_databases; Query OK, 0 rows Affected (0.00 sec) mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Test |+--------------------+3 rows in Set (0.00 sec) mysql>

From:

Mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Test |+--------------------+3 rows in Set (0.00 sec) mysql>

This article is from the "longing for Technology small white" blog, please make sure to keep this source http://lesliecheung.blog.51cto.com/12622169/1958255

MySQL Master-slave replication