Mysql实现主从复制

Mysql实现主从复制

实验环境：

server5：主
server6：从
server7：从
iptabls off
selinux Disabled

安装mysql

*先查看是否有其它版本的,若有，则先卸载

*先查看是否有其它版本的,若有，则先卸载
[root@server5 ~]# rpm -qa | grep mysql
mysql-libs-5.1.71-1.el6.x86_64
[root@server5 ~]# rpm -e mysql-libs-5.1.71-1.el6.x86_64
error: Failed dependencies:
    libmysqlclient.so.16()(64bit) is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64
    libmysqlclient.so.16(libmysqlclient_16)(64bit) is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64
    mysql-libs is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64
[root@server5 ~]# rpm -e --nodeps mysql-libs-5.1.71-1.el6.x86_64
[root@server5 ~]# rpm -qa | grep mysql
[root@server5 ~]#

*安装mysql

[root@server5 ~]# tar zxf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar 
 
gzip: stdin: not in gzip format
tar: Child returned status 1
tar: Error is not recoverable: exiting now
[root@server5 ~]# tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar 
#注意顺序
[root@server5 ~]# rpm -ivh mysql-community-common-5.7.17-1.el6.x86_64.rpm
[root@server5 ~]# rpm -ivh mysql-community-libs-5.7.17-1.el6.x86_64.rpm 
[root@server5 ~]# rpm -ivh mysql-community-devel-5.7.17-1.el6.x86_64.rpm
[root@server5 ~]# yum install libaio -y
[root@server5 ~]# yum install numactl -y
[root@server5 ~]# rpm -ivh mysql-community-client-5.7.17-1.el6.x86_64.rpm
[root@server5 ~]# rpm -ivh mysql-community-server-5.7.17-1.el6.x86_64.rpm
warning: mysql-community-server-5.7.17-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
    /usr/bin/perl is needed by mysql-community-server-5.7.17-1.el6.x86_64
    perl(File::Path) is needed by mysql-community-server-5.7.17-1.el6.x86_64
    perl(Getopt::Long) is needed by mysql-community-server-5.7.17-1.el6.x86_64
    perl(POSIX) is needed by mysql-community-server-5.7.17-1.el6.x86_64
    perl(strict) is needed by mysql-community-server-5.7.17-1.el6.x86_64
[root@server5 ~]# yum install perl  -y
[root@server5 ~]# rpm -ivh mysql-community-server-5.7.17-1.el6.x86_64.rpm
[root@server5 ~]#

*在server5：master

[root@server5 ~]# vim /etc/my.cnf
.....
log-bin=mysql-bin
binlog-do-db=test
server-id=1
binlog-ignore-db=mysql
.....
[root@server5 ~]# /etc/init.d/mysqld start
Initializing MySQL database:                               [  OK  ]
Installing validate password plugin:                       [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@server5 ~]#
 
 
[root@server5 ~]# cat /var/log/mysqld.log | grep pass  #过滤初始密码
2017-06-13T01:22:16.400573Z 1 [Note] A temporary password is generated for root@localhost: M.uepxaHq3oI
[root@server5 ~]# mysql_secure_installation        #修改mysql默认root密码
[root@server5 ~]# mysql -uroot -pXiamin+0099
mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO westos@'172.25.66.7'
    -> IDENTIFIED BY 'Xiamin+0099';         #创建账户并给与权限
Query OK, 0 rows affected, 1 warning (0.07 sec)
 
mysql> Flush privileges;
Query OK, 0 rows affected (0.12 sec)
 
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      616 | test         | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 
mysql> quit
Bye
[root@server5 ~]#

*在server7：slave

[root@server7 ~]# vim /etc/my.cnf
.....
server-id=2
.....
[root@server7 ~]# /etc/init.d/mysqld start
Initializing MySQL database:                               [  OK  ]
Installing validate password plugin:                       [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@server5 ~]#
 
[root@server7 ~]# mysql_secure_installation
[root@server7 ~]# mysql -uroot -pXiamin+0099
(此时需要先stop slave)
mysql> change master to master_host='172.25.66.5', master_user='westos',
    -> master_password='Xiamin+0099', master_log_file='mysql-bin.000005', master_log_pos=616;
Query OK, 0 rows affected, 2 warnings (0.50 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.06 sec)
 
mysql> show slave status\G;
.....
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
.....
mysql> quit
Bye
[root@server7 ~]#