MySQL master-slave replication Configuration Chapter

Operating system Environment: CentOS release 6.5 (Final)

MySQL database: MySQL Ver 14.14 distrib 5.5.45

Master 192.168.10.180

Slave 192.168.10.80

The hosts of the master and slave servers have added IP hostname correspondence

The general steps are as follows

1. Server role designation

2.mysql Database Installation Configuration

3. Configuration on the main library

3.1 Set Server-id and turn on Binlog

3.2 Create user for synchronization, test whether can connect

3.3 To Database lock table read-only

3.4 Viewing the main library status

3.5 Master Fully prepared

3.6 Transfer Backup to Slave

4. From the configuration on the library

4.1 Setting Server-id

4.2 Restoring a full standby from master

4.3 Configuring synchronization parameters

4.4 Testing

Step 2 Refer to the previous installation configuration, this time is based on the CMake compiled installation of the operation

Turn on Boot

[Email protected] ~]# chkconfig--list mysqldmysqld 0:off1:off2:on3:on4:on5:on6:off[[email protected] ~]# chkconf IG--list mysqldmysqld 0:off1:off2:on3:on4:on5:on6:off

3.1 Modifying the parameter file

[Email protected] ~]# vi/etc/my.cnf Server-id = 1log-bin =/app/mysql-5.5.45/data/mysql-bin[[email protected] ~]# grep- E "Server-id|log-bin"/etc/my.cnf log-bin =/app/mysql-5.5.45/data/mysql-binserver-id= 1#log-bin=mysql-bin[[email Protected] ~]# service mysqld restartshutting down MySQL. success! Starting MySQL. success!

Note the configuration restarts the database under the Mysqld module

3.2 Creating a user, testing

[[email protected] ~]# mysql-uroot-prootrootmysql> grant replication Slave on * * to ' slave_rep ' @ ' 192.168.10.80 ' Iden Tified by ' 123456 '; Query OK, 0 rows Affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows Affected (0.00 sec) Replication Slave is a required permission, *. * represents all tables, or you can specify a specific library slave_rep ' @ ' 192.168.10.80 ' Test from library for user name and specified IP: [[email protected] ~]# mysql-uslave_rep-p123456-h 192.168.10.180

3.3 To Database lock table read-only

mysql> show master status ;+------------------+----------+--------------+-------------- ----+| file             | position  | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+--- ---------------+| mysql-bin.000001 |     1135 |               |                   |+------------------+----------+------------ --+------------------+1 row in set  (0.00 sec) mysql> show master  logs ;+------------------+-----------+| log_name          | file_size |+------------------+-----------+| mysql-bin.000001 |       1135 |+------------------+-----------+1 row in set  (0.00 sec) mysql> flush  table with read lock ; query ok, 0 rows affected  (0.01 SEC)

Note that the 5.1 and 5.5 lock tables have different tables and table

Open new terminal for full standby

[Email protected] ~]# mysqldump-uroot-prootroot-a-B--events--master-data=2|gzip >/tmp/mysql_rep.sql.gz

Unlock the main library

mysql> unlock tables; Query OK, 0 rows Affected (0.00 sec) [[email protected] ~]# scp/tmp/mysql_rep.sql.gz 192.168.10.80:/tmp

Configure from Library

4.1 Setting Server-id

[Email protected] ~]# VI/ETC/MY.CNF [[email protected] ~]# grep-e "Server-id"/etc/my.cnf server-id= 3[[email protected ] ~]# service mysqld restartshutting down MySQL. success! Starting MySQL. success! [Email protected] ~]# [[email protected] ~]# mysql-uroot-prootroot-e "show variables like '%server_id% ';" +---------------+-------+| variable_name | Value |+---------------+-------+| server_id | 3 |+---------------+-------+

4.2 Restoring a full standby from master

[Email protected] ~]# gunzip/tmp/mysql_rep.sql.gz [[email protected] ~]# Mysql-uroot-prootroot </tmp/mysql_rep.sql

4.3 Configuring synchronization parameters

Mysql> Change Master to-master_host= ' 192.168.10.180 ', master_port=3306, master_user= ' Slave_r EP ', master_password= ' 123456 ',---master_log_file= ' mysql-bin.000001 ', master_log_pos=1135; Query OK, 0 rows affected (0.07 sec)

The information generated by the above operation is stored in the/app/mysql-5.5.45/data/master_info in the data directory and is updated after receiving from the main library.

4.4 start slave

mysql> start slave ; query ok, 0 rows affected  (0.00 sec) mysql> show slave status\g  ;*************************** 1. row ***************************                Slave_IO_State: Waiting for  master to send event                   Master_Host: 192.168.10.180                   Master_User: slave_rep                   master_port:  3306                 connect_retry: 60               master_log_file: mysql-bin.000001          read_ master_log_pos: 1135                Relay_Log_File: slave-relay-bin.000002                 Relay_Log_Pos: 253         relay_master_log_file: mysql-bin.000001              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: 1135               Relay_Log_Space: 409               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 :  0               last_sql_ error:   replicate_ignore_server_ids:               Master_Server_Id: 11 row in set  (0.00 sec) ERROR:  no query specified Relay log information from the library [[Email protected] data]# cat relay-log.info  ./slave-relay-bin.000002253mysql-bin.0000011135

4.5 Testing

Main Library CREATE database, build table, insert data, view synchronization information from library

[Email protected] ~]# mysql-uroot-prootrootmysql> CREATE DATABASE rep1; Query OK, 1 row Affected (0.00 sec) mysql> use REP1;D atabase changedmysql> CREATE TABLE test (num int); Query OK, 0 rows affected (0.01 sec) mysql> insert into test values (1); Query OK, 1 row affected (0.01 sec) mysql> insert INTO test values (2); Query OK, 1 row Affected (0.00 sec)

View from Library

[[email protected] ~]# mysql -uroot -prootroot mysql> show  databases ;+--------------------+| database            |+--------------------+| information_schema | |  mysql              | |  performance_schema | |  rep1               | |  test_rep           |+--------------------+5  rows in set  (0.00 secmysql> select * from rep1.test ;+------+|  num  |+------+|    1 | |     2 |+------+2 rows in set  (0.00 sec) View the trunk log information [[email  protected] data]# cat relay-log.info ./slave-relay-bin.000002794mysql-bin.0000011676[[email protected] data]#[[email protected] data]# mysqlbinlog  Slave-relay-bin.000002 from the library information mysql> show processlist\g ;*************************** 1.  row ***************************     id: 17   user:  system user   Host:      db: NULLCommand:  Connect   time: 831  state: waiting for master to send  event   info: null*************************** 2. row ****************      Id: 18   User: system user    Host:      db: NULLCommand: Connect   Time:  373  state: slave has read all relay log; waiting for  The slave i/o threaD to update it   info: null*************************** 3. row  ***************************     id: 19   user: root    Host: localhost     db: NULLCommand: Query    Time: 0  State: NULL   Info: show processlist3  rows in set  (0.00 sec) error: no query specified Main Library information mysql> show  processlist\G ;*************************** 1. row ***************************      id: 8   user: slave_rep   host: slave :53940     db: nullcommand: binlog dump   time:  867  state: master has sent all binlog to slave; waiting  for binlog to be updated   info: null*************************** 2. row ***************      id: 9   user: root   host:  localhost     db: rep1command: query   time: 0   state: null   info: show processlist2 rows in set   (0.00 SEC) error: no query specified

This article is from "The girl said" blog, please be sure to keep this source http://sugarlovecxq.blog.51cto.com/6707742/1697139

MySQL master-slave replication Configuration Chapter