专栏文章

CentOS6下部署MySQL主从（AB）复制

于小姐家的小小赵🥋 2017-02-10

感谢原文作者：刘琪原文地址：http://www.cnblogs.com/liuqi-guiyin/articles/6386003.html 此文只用来做笔记

[root@localhost ~]# yum groupinstall "开发工具" -y

[root@localhost ~]# chkconfig iptables off

[root@localhost ~]# chkconfig --list iptables
iptables 0:关闭 1:关闭 2:关闭 3:关闭 4:关闭 5:关闭 6:关闭

[root@localhost ~]# yum install wget vim ncurses-devel -y

[root@localhost ~]# cat /etc/selinux/config | head -7 | tail -1 | sed -i s@enforcing@disabled@g /etc/selinux/config

[root@localhost ~]# hostname Mysql-Master

[root@localhost ~]# cat /etc/sysconfig/network | sed -i s@localhost.localdomain@Mysql-Master@g /etc/sysconfig/network

[root@localhost ~]# reboot
重要说明：为什么要重启？因为修改SElinux为永久性关闭！所以必须要重启才能生效！

[root@Mysql-Master src]# tar xvzf cmake-3.5.2.tar.gz

[root@Mysql-Master cmake-3.5.2]# ./configure --prefix=/usr/local/cmake && make && make install

[root@Mysql-Master src]# tar xvzf mysql-5.6.34.tar.gz

[root@Mysql-Master mysql-5.6.34]# /usr/local/cmake/bin/cmake \
>   -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
>   -DMYSQL_DATADIR=/usr/local/mysql/data \
>   -DSYSCONFDIR=/etc \
>   -DWITH_READLINE=1 \
>   -DMYSQL_TCP_PORT=3306 \
>   -DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
>   -DENABLED_LOCAL_INFILE=1 \
>   -DEXTRA_CHARSETS=all \
>   -DDEFAULT_CHARSET=utf8 \
>   -DDEFAULT_COLLATION=utf8_general_ci

[root@Mysql-Master mysql-5.6.34]# make && make install

[root@Mysql-Master mysql-5.6.34]# rpm -qa | grep mysql
mysql-libs-5.1.73-7.el6.x86_64
重点说明：如果rpm -qa没有检测出“mysql-libs-5.1.73-7.el6.x86_64”这个包？就不用执行yum remove这条命令啦！注意！如果包含任何mysql的包，就逐个删除

[root@Mysql-Master mysql-5.6.34]# yum remove mysql-libs-5.1.73-7.el6.x86_64 -y

[root@Mysql-Master mysql-5.6.34]# useradd -M -u 27 -s /sbin/nologin mysql

[root@Mysql-Master mysql-5.6.34]# groupmod -g 27 mysql

[root@Mysql-Master mysql-5.6.34]# chown mysql.mysql -R /usr/local/mysql/

[root@Mysql-Master mysql-5.6.34]# echo '#Mysql-5.6.34' >> /etc/profile

[root@Mysql-Master mysql-5.6.34]# echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile

[root@Mysql-Master mysql-5.6.34]# source /etc/profile

[root@Mysql-Master mysql-5.6.34]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

[root@Mysql-Master mysql-5.6.34]# chmod +x /etc/init.d/mysqld

[root@Mysql-Master mysql-5.6.34]# chkconfig --add mysqld

[root@Mysql-Master mysql-5.6.34]# chkconfig --list mysqld
mysqld 0:关闭 1:关闭 2:启用 3:启用 4:启用 5:启用 6:关闭

[root@Mysql-Master mysql-5.6.34]# chown mysql.mysql -R /usr/local/mysql/data/

[root@Mysql-Master mysql-5.6.34]# cd /usr/local/mysql/

[root@Mysql-Master mysql]# pwd
/usr/local/mysql

[root@Mysql-Master mysql]# ./scripts/mysql_install_db --user=mysql

[root@Mysql-Master mysql]# service mysqld start
Starting MySQL. SUCCESS!

[root@Mysql-Master mysql]# /usr/local/mysql/bin/mysqladmin -u root password "123456"

[root@Mysql-Master mysql]# /usr/local/mysql/bin/mysql -u root -pliuqi

[root@Mysql-Master mysql]# pwd
/usr/local/mysql

[root@Mysql-Master mysql]# cp /usr/local/mysql/my.cnf /etc/my.cnf

[root@Mysql-Master mysql]# cat >/etc/my.cnf<<EOF
> [mysqld]
> log-bin=master-bin
> server-id = 1
> EOF

[root@Mysql-Master mysql]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

[root@Mysql-Master mysql]# /usr/local/mysql/bin/mysql -u root -p123456

Mysql > grant replication slave on *.* to backup@10.10.0.145 identified by 'Password';
Mysql > flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 | 429 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

部署Mysql-Slave服务器（IP地址：192.168.31.92）

[root@localhost ~]# yum groupinstall "开发工具" -y

[root@localhost ~]# chkconfig iptables off

[root@localhost ~]# chkconfig --list iptables
iptables 0:关闭 1:关闭 2:关闭 3:关闭 4:关闭 5:关闭 6:关闭

[root@localhost ~]# yum install wget vim ncurses-devel -y

[root@localhost ~]# cat /etc/selinux/config | head -7 | tail -1 | sed -i s@enforcing@disabled@g /etc/selinux/config

[root@localhost ~]# hostname Mysql-Slave

[root@localhost ~]# cat /etc/sysconfig/network | sed -i s@localhost.localdomain@Mysql-Slave@g /etc/sysconfig/network

[root@localhost ~]# reboot
重要说明：为什么要重启？因为修改SElinux为永久性关闭！所以必须要重启才能生效！

[root@Mysql-Slave src]# tar xvzf cmake-3.5.2.tar.gz

[root@Mysql-Slave cmake-3.5.2]# ./configure --prefix=/usr/local/cmake && make && make install

[root@Mysql-Slave src]# tar xvzf mysql-5.6.34.tar.gz

[root@Mysql-Slave mysql-5.6.34]# /usr/local/cmake/bin/cmake \
>   -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
>   -DMYSQL_DATADIR=/usr/local/mysql/data \
>   -DSYSCONFDIR=/etc \
>   -DWITH_READLINE=1 \
>   -DMYSQL_TCP_PORT=3306 \
>   -DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
>   -DENABLED_LOCAL_INFILE=1 \
>   -DEXTRA_CHARSETS=all \
>   -DDEFAULT_CHARSET=utf8 \
>   -DDEFAULT_COLLATION=utf8_general_ci

[root@Mysql-Slave mysql-5.6.34]# make && make install

[root@Mysql-Slave mysql-5.6.34]# rpm -qa | grep mysql
mysql-libs-5.1.73-7.el6.x86_64
重点说明：如果rpm -qa没有检测出“mysql-libs-5.1.73-7.el6.x86_64”这个包？就不用执行yum remove这条命令啦！

[root@Mysql-Slave mysql-5.6.34]# yum remove mysql-libs-5.1.73-7.el6.x86_64 -y

[root@Mysql-Slave mysql-5.6.34]# useradd -M -u 27 -s /sbin/nologin mysql

[root@Mysql-Slave mysql-5.6.34]# groupmod -g 27 mysql

[root@Mysql-Slave mysql-5.6.34]# chown mysql.mysql -R /usr/local/mysql/

[root@Mysql-Slave mysql-5.6.34]# echo '#Mysql-5.6.34' >> /etc/profile

[root@Mysql-Slave mysql-5.6.34]# echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile

[root@Mysql-Slave mysql-5.6.34]# source /etc/profile

[root@Mysql-Slave mysql-5.6.34]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

[root@Mysql-Slave mysql-5.6.34]# chmod +x /etc/init.d/mysqld

[root@Mysql-Slave mysql-5.6.34]# chkconfig --add mysqld

[root@Mysql-Slave mysql-5.6.34]# chkconfig --list mysqld
mysqld 0:关闭 1:关闭 2:启用 3:启用 4:启用 5:启用 6:关闭

[root@Mysql-Slave mysql-5.6.34]# chown mysql.mysql -R /usr/local/mysql/data/

[root@Mysql-Slave mysql-5.6.34]# cd /usr/local/mysql/

[root@Mysql-Slave mysql]# pwd
/usr/local/mysql

[root@Mysql-Slave mysql]# ./scripts/mysql_install_db --user=mysql

[root@Mysql-Slave mysql]# service mysqld start
Starting MySQL. SUCCESS!

[root@Mysql-Slave mysql]# /usr/local/mysql/bin/mysqladmin -u root password "liuqi"

[root@Mysql-Slave mysql]# /usr/local/mysql/bin/mysql -u root -pliuqi

[root@Mysql-Slave mysql]# cp /usr/local/mysql/my.cnf /etc/my.cnf

[root@Mysql-Slave mysql]# cat >/etc/my.cnf<<EOF
> [mysqld]
> server-id = 2
> EOF

[root@Mysql-Slave mysql]# service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS!

[root@Mysql-Slave mysql]# /usr/local/mysql/bin/mysql -u root -pliuqi

重点说明：下面所有的操作必须先要完成Mysql-Master服务器的部署才可以操作！！！

　　　　　master-bin.000002　　429　　这些数字编号不是随便取的，而是要对照Mysql-Master给出的编号进行填写！！！　

　　　　　在整体主从配置过程中出现　　“Slave_IO_Running: Yes”　　“Slave_SQL_Running: Yes”　　这个？证明Mysql主从配置成功！缺少一个Yes就是失败！　

mysql> change master to
    -> master_host='192.168.31.91',
    -> master_user='liuqi',
    -> master_password='liuqi',
    -> master_log_file='master-bin.000002',
    -> master_log_pos=429;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.31.91
                  Master_User: liuqi
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 429
               Relay_Log_File: Mysql-Slave-relay-bin.000002
                Relay_Log_Pos: 284
        Relay_Master_Log_File: master-bin.000002
             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: 429
              Relay_Log_Space: 463
              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: 1
                  Master_UUID: db5f8249-ee21-11e6-a3ad-000c29d8ff0b
             Master_Info_File: /usr/local/mysql/data/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)

对Mysql主从进行测试！

下面这些是在Mysql-Slave服务器上进行测试！

# Linux、运维、jenkins # 长文章

版权归作者所有，转载请注明出处

于小姐家的小小赵🥋 关注

热度 0

LOFTER-网易轻博