首页 / MYSQL / MySql主从复制实验

MySql主从复制实验

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySql主从复制实验，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6114字，纯文字阅读大概需要9分钟。

内容图文

实验环境准备：

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命令可以查看二进制文件.

 

原文：http://www.cnblogs.com/home-beibei/p/4323577.html

内容总结

以上是互联网集市为您收集整理的MySql主从复制实验全部内容，希望文章能够帮你解决MySql主从复制实验所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/1045521.html

来源：【匿名】