MySql主从复制实验

实验环境准备:

IP 主机名 角色
133.72.24.201 hadoop001 master
133.72.24.202 hadoop002 slave
Mysql数据库软件版本 MySQL-server-5.6.23-1.el6.x86_64
操作系统 CentOS release 6.4 (Final)
内核版本 2.6.32-358.el6.x86_64
1、卸载原有 系统的mysql组件
[root@hadoop001 Downloads]# rpm -qa | grep -i mysql
mysql-libs-5.1.66-2.el6_3.x86_64
[root@hadoop001 Downloads]# rpm -e mysql-libs-5.1.66-2.el6_3.x86_64 --nodeps
2、安装mysql
[root@hadoop001 Downloads]# rpm -ivh MySQL-server-5.6.23-1.el6.x86_64
 
A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in ‘/root/.mysql_secret‘.
 
You must change that password on your first connect,
no other statement but ‘SET PASSWORD‘ will be accepted.
 
[root@hadoop001 Downloads]# rpm -ivh MySQL-client-5.6.23-1.el6.x86_64.rpm
Preparing...                ########################################### [100%]
   1:MySQL-client           ########################################### [100%]
 
3、修改配置文件启动mysql服务!
[root@hadoop001 Downloads]# echo ‘explicit_defaults_for_timestamp=true‘ >> /usr/my.cnf
[root@hadoop001 Downloads]# service mysql start
Starting MySQL... SUCCESS!
4、登录mysql数据库并修改密码
[root@hadoop001 Downloads]# cat /root/.mysql_secret
# The random password set for the root user at Thu Mar  5 23:07:44 2015 (local time): 60MhiIwDfYxoNOEA
[root@hadoop001 Downloads]# mysql -u root -p60MhiIwDfYxoNOEA
修改密码:
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.00 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设置
    5.1、停止mysql服务
     service mysql stop
    5.2、vi /usr/my.cnf
技术分享
 1     # log_bin,必须开启bin日志.
 2     log_bin=master-bin.log
 3     # server_id = .....必须设置server_id,保证集群内的唯一性.可以用ip地址最后3位
 4     server_id=201
 5     log-slave-updates
 6     #所有事务提交必须写入硬盘
 7     innodb_flush_log_at_trx_commit=1
 8     #必须同步bin日志
 9     sync_binlog=1
10     binlog_format=mixed
11     max_connections=1000
12     relay-log=master-relay-bin
13     master-info-repository=table
14     relay-log-info-repository=table
15     relay-log-recovery=1
View Code
6、slave设置
    6.1、停止mysql服务
     service mysql stop
    6.2、vi /usr/my.cnf
技术分享
 1 # log_bin,slave节点也需要开启bin日志
 2 log_bin=slave-bin.log
 3 # server_id = .....必须设置server_id,保证集群内的唯一性.可以用ip地址最后3位
 4 server_id=202
 5 #保证slave节点也能同步刷新master发送bin日志.
 6 log-slave-updates
 7 binlog_format=mixed
 8 max_connections=1000
 9 relay-log=slave-relay-bin
10 master-info-repository=table
11 relay-log-info-repository=table
12 relay-log-recovery=1
View Code
7、master节点创建复制权限用户
mysql>GRANT REPLICATION SLAVE,reload,super ON *.* TO repl_user@hadoop002 IDENTIFIED BY ‘repl_password‘;
mysql>flush PRIVILEGES;
8、全库备份
    8.1、master节点执行全量备份
    8.1.1、先设置全局表锁
mysql>FLUSH TABLES WITH READ LOCK;
    8.1.2、记录当前日志与位置
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、执行备份命令
[root@hadoop001 ~]# mysqldump -uroot -pmysql123 test > test.sql
    8.1.4、执行命令释放锁    
mysql>UNLOCK TABLES;
##退出mysqlclient端之后才能真正释放锁
mysql>exit
    8.1.5、把备份脚本传递到slave节点
[root@hadoop001 ~]#scp test.sql root@hadoop002:/root/test.sql  
 
    8.2、slave节点执行恢复全库
[root@hadoop002 ~]#mysql -uroot -pmysql123 -D test < /root/test.sql
 
    8.3、slave节点创建replication连接
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、检测replication状态
    9.1、master节点检测从节点
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节点检测replication状态
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
               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: 0
Master_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-000c29c0a8e2
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_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: 0
1 row in set (0.00 sec)
 
10、验证
    10.1、检测数据是否同步
    从master建表并插入数据,然后查看slave节点是否同步.
    10.2、检测binlog是否同步
    查看master进行更新是,slave的binlog是否也在同步更新.
    mysqlbinlog命令可以查看二进制文件.

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。