mysql-(master-slave) configuration

1, two machines, install a consistent version of MySQL

192.168.29.128 (Master) MySQL-5.6.21

192.168.29.129 (slave) MySQL-5.6.21

2. Configure Master

Configure binary log and server ID

[[Email protected] ~] # vi/etc/my.cnf

The configuration is as follows:

[Mysqld]
log-bin=mysql-bin Server-id=1

Restart MySQL

3, configuration slave

[mysqld] Server-id=2

4. Create an account for replication on master

Mysql> Create User 'Repl'@'%'Identified by '123456'; Query OK,0Rows Affected (0.13sec) MySQL> Grant ReplicationSlave on *.*  to 'Repl'@'%' ; Query OK,0Rows Affected (0.02Sec

5. View binary log file name and position on master

Mysql>show master status;+------------------+----------+--------------+------------------+-------------------+| File             |Position|binlog_do_db|binlog_ignore_db|Executed_gtid_set|+------------------+----------+--------------+------------------+-------------------+|Mysql-Bin.000003 |       - |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1Rowinch Set(0.00Sec

6, configuration slave

Mysql>Change Master toMaster_host='192.168.29.128', Master_user='Repl', Master_password='123456', Master_port=3306, Master_log_file='mysql-bin.000003', Master_log_pos= -, Master_connect_retry=Ten; Query OK,0Rows affected,2Warnings (0.30Sec

Using the account created above for replication, Master_log_file uses the file name queried on master, and Master_log_pos must also use the query on master position

7. Start the copy function on slave

MySQL>0 rows affected (0.01 sec)

8, check the status of slave

Mysql>show slave status\g;*************************** 1. Row***************************slave_io_state:waiting forMaster toSend event Master_host:192.168.29.128master_user:repl Master_port:3306Connect_retry:TenMaster_log_file:mysql-Bin.000003Read_master_log_pos: -Relay_log_file:luxh- Geneva-Relay-Bin.000002Relay_log_pos:283Relay_master_log_file:mysql-Bin.000003Slave_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:0Last_error:skip_counter:0Exec_master_log_pos: -Relay_log_space:458until_condition:none Until_log_file:until_log_pos:0Master_ssl_allowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SS L_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0master_ssl_verify_server_cert:no Last_io_errno:0Last_io_error:last_sql_errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:1master_uuid:9567210c-684f-11e4-b062-000c29398b4c master_info_file:/var/Lib/Mysql/master.info Sql_delay:0Sql_remaining_delay:NULLSlave_sql_running_state:slave hasRead  AllRelayLog; Waiting forThe slave I/O thread to UpdateIt master_retry_count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL _crl:master_ssl_crlpath:retrieved_gtid_set:executed_gtid_set:auto_po Sition:01Rowinch Set(0.00Sec

9, configuration is complete. Test the effect.

1) Execute the following statement on Master

Mysql> Create Databaseweb_db; Query OK,1Row affected (0.04sec) MySQL>  Useweb_db;DatabaseChangedmysql> Create TableT_user (IDint Primary KeyAuto_increment,usernamevarchar( +)); Query OK,0Rows Affected (0.13sec) MySQL> Insert  intoT_user (username)Values('Lihuai'); Query OK,1Row affected (0.05sec) MySQL> Select *  fromT_user;+----+----------+|Id|Username|+----+----------+|  1 |Lihuai|+----+----------+1Rowinch Set(0.00Sec

2) check on slave to see if the copy was successful

Mysql>show databases;+--------------------+| Database           |+--------------------+|Information_schema||Mysql||Performance_schema||Test||web_db|+--------------------+5Rowsinch Set(0.05sec) MySQL>  Useweb_db; ReadingTableInformation forCompletion of Table  and columnnamesyou can turnifThis feature toGet a quicker startup with -ADatabaseChangedmysql>show tables;+------------------+|tables_in_web_db|+------------------+|T_user|+------------------+1Rowinch Set(0.00sec) MySQL> Select *  fromT_user;+----+----------+|Id|Username|+----+----------+|  1 |Lihuai|+----+----------+1Rowinch Set(0.00Sec

10, the above configuration is for the new installation of MySQL server from replication, but also for the database of existing data, or the existing master-slave structure to add slave

Specific configuration, you can refer to the official website documentation, very detailed.

　Http://dev.mysql.com/doc/refman/5.6/en/replication-howto.html

mysql-(master-slave) configuration