MySQL master-slave replication experiment

Lab Environment Preparation:

Ip	Host Name	Role
133.72.24.201	hadoop001	Master
133.72.24.202	hadoop002	Slave

MySQL database software version	mysql-se rver-5.6.23-1.el6.x86_64
operating system	CentOS release 6.4 (Final)
kernel version	2.6.32-358.el6.x86_64

1, uninstall the original system MySQL components [[email protected] downloads]# Rpm-qa | Grep-i Mysqlmysql-libs-5.1.66-2.el6_3.x86_64[[email protected] downloads]# rpm-e mysql-libs-5.1.66-2.el6_3.x86_64-- NODEPS2, installation Mysql[[email protected] downloads]# rpm-ivh mysql-server-5.6.23-1.el6.x86_64 A RANDOM PASSWORD have BEEN SET for The MySQL root USER! You'll find that password in'/root/.mysql_secret '.You must change this password on your first connect,no other statement but' SET PASSWORD' 'll be accepted. [[email protected] downloads]# RPM-IVH mysql-client-5.6.23-1.el6.x86_64.rpmpreparing ...                ########################################### [100%]  & Nbsp;1:mysql-client           ########################################### [100%] 3, Modify the configuration file to start the MySQL service! [[email protected] downloads]# echo ' explicit_defaults_for_timestamp=true ' >>/usr/my.cnf[[email  Protected] downloads]# service mysql startstarting mysql ... Success!4, log in to the MySQL database and change the password [[email protected] downloads]# cat/root/.mysql_secret# The random password set for the Root user at Thu Mar  5 23:07:44 (local time): 60mhiiwdfyxonoea[[email protected] downloads]# mysql-u root -p60mhiiwdfyxonoea Change Password:mysql> SET PASSWORD for ' root ' @ ' localhost ' = PASSWORD (' mysql123 '); Query OK, 0 rows Affected (0.00 sec)  mysql> SET PASSWORD for ' root ' @ ':: 1 ' = PASSWORD (' mysql123 '); Query OK, 0 rows affected (0. xx sec)  mysql> SET PASSWORD for ' root ' @ ' 127.0.0.1 ' = PASSWORD (' mysql123 '); Query OK, 0 rows Affected (0.00 sec)  mysql> SET PASSWORD for ' root ' @ ' hadoop001 ' = PASSWORD (' mysql123 '); Query OK, 0 rows Affected (0.00 sec)  mysql> GRANT all privileges on * * to ' root ' @ '% ' identified by ' mysql123 ' with GRANT OPTION; Query OK, 0 rows Affected (0.00 sec)  mysql> flush privileges; Query OK, 0 rows Affected (0.00 sec)   5. Master Settings5.1. Stop MySQL serviceservice MySQL Stop5.2.Vi/usr/my.cnf

1 # Log_bin, the bin log must be turned on.2 Log_bin=master-bin.log3 # server_id = ... SERVER_ID must be set to ensure uniqueness within the cluster. IP address can be used last 3 bits4 server_id=2015 log-slave-updates6 #所有事务提交必须写入硬盘7 innodb_flush_log_at_trx_commit=18 #必须同步bin日志9 sync_binlog=1Ten binlog_format=mixed One max_connections=1000 A Relay-log=master-relay-bin - master-info-repository=table - relay-log-info-repository=table theRelay-log-recovery=1

View Code6. Slave setting6.1. Stop MySQL serviceservice MySQL Stop6.2.vi/usr/my.cnf

1 # Log_bin,slave node also needs to open the bin log2 Log_bin=slave-bin.log3 # server_id = ... SERVER_ID must be set to ensure uniqueness within the cluster. IP address can be used last 3 bits4 server_id=2025 #保证slave节点也能同步刷新master发送bin日志.6 log-slave-updates7 binlog_format=mixed8 max_connections=10009 Relay-log=slave-relay-binTen master-info-repository=table One relay-log-info-repository=table ARelay-log-recovery=1

View Code7, master node create copy rights user Mysql>grant REPLICATION slave,reload,super on * * to [email protected] identified by ' Repl_password '; Mysql>flush privileges;8, full library backup8.1.Master node performs full-scale backup8.1.1,First set global table lock Mysql>flush TABLES with READ lock;8.1.2,Record current log and location mysql> Show Master status;+-------------------+----------+--------------+------------------+--------- ----------+| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+-------------------+----------+--------------+------------------+-------------------+|      master-bin.000001 |              425 |                  |                   | |+-------------------+----------+--------------+------------------+-------------------+1 row in Set (0.00 sec)8.1.3, performing backup commands[Email protected] ~]# mysqldump-uroot-pmysql123 Test > Test.sql8.1.4, execute command release lockMysql>unlock tables;# #退出mysqlclient端之后才能真正释放锁mysql >exit8.1.5, passing the backup script to the slave node[[Email protected] ~] #scp test.sql [email protected]:/root/test.sql8.2.Slave node execution recovery Full library [[email protected] ~] #mysql-uroot-pmysql123-d Test </root/test.sql8.3.Slave node Create replication connection Mysql>change MASTER to master_host= ' 133.72.24.201 ', master_user= ' Repl_user ', master_ Password= ' Repl_password ', master_log_file= ' master-bin.000001 ', master_log_pos= 425;mysql>start slave;9.Detecting Replication Status9.1.Master node detection from node mysql> show slave hosts;+-----------+------+------+-----------+---------------------------------- ----+| server_id | Host | Port | master_id |       Slave_uuid |+-----------+------+------+-----------+--------------------------------------+|      202 | |       3306 | 201 | AA5BA5ED-C5FF-11E4-934F-000C29DBB4C6 |+-----------+------+------+-----------+----------------------------------- ---+1 row in Set (0.00 sec)9.2.Slave node detects replication status mysql> show slave status\g*************************** 1. Row ***************************                slave_io_state:waiting for Master to send event                   master_host:133.72.24.201                   master_user:repl_user                   master_port:3306                 connect_retry:60               master_log_file:master-bin.000001           read_master_log_pos:1426              &nbsp relay_log_file:slave-relay-bin.000002                 relay_log_pos:1285        Relay_master_log_file: master-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:1426              relay_log_space:1458              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:201 master_uuid:3ff67e00-c3d0-11e4-850f-000c29c0a8e2Master_Info_File:mysql.slave_master_info sql_delay:0 Sql_remaining_delay:nullSlave_sql_running_state:slave have read all relay log; Waiting for the Slave I/O thread to update itmaster_retry_count:86400 Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:                Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set: Auto_position:01 row in Set (0.00 sec) 10, validation10.1. Whether the test data is synchronizedbuild the table from master and insert the data, and then see if the slave node is synchronized.10.2, check whether the Binlog synchronizationView Master to update Yes, slave binlog is also synchronizing updates.The mysqlbinlog command allows you to view the binaries.

MySQL master-slave replication experiment