Mysql实现主从复制

最新推荐文章于 2025-12-29 17:07:25 发布

原创最新推荐文章于 2025-12-29 17:07:25 发布 · 348 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

本文详细介绍了如何在server5作为主服务器，server6和server7作为从服务器的情况下配置MySQL的主从复制。主要内容包括：环境准备，如关闭iptables和禁用selinux；MySQL的安装过程及注意事项；配置文件my.cnf的设置；启动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> 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 ~]#

测试：

在master中新建test库
[root@server5 ~]# mysql -uroot -pXiamin+0099
.....
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.07 sec)
mysql> create database test;
Query OK, 1 row affected (0.08 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test
Database changed
mysql> create table MyClass(
    -> id int(4) not null primary key auto_increment,
    -> name char(20) not null,
    -> sex int(4) not null default '0',
    -> degree double(16,2));
Query OK, 0 rows affected (1.20 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| MyClass        |
+----------------+
1 row in set (0.00 sec)

mysql> show processlist;
+----+--------+---------------+------+-------------+-------+---------------------------------------------------------------+------------------+
| Id | User   | Host          | db   | Command     | Time  | State                                                         | Info             |
+----+--------+---------------+------+-------------+-------+---------------------------------------------------------------+------------------+
|  3 | westos | server7:50403 | NULL | Binlog Dump | 17034 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 14 | root   | localhost     | NULL | Query       |     0 | starting                                                      | show processlist |
+----+--------+---------------+------+-------------+-------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

mysql> quit
Bye

.....

[root@server5 ~]#

在slave上查看

[root@server7 ~]# mysql -uroot -pXiamin+0099
.....
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| MyClass        |
+----------------+
1 row in set (0.00 sec)
mysql> show processlist;
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time  | State                                                  | Info             |
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
|  1 | system user |           | NULL | Connect |  1894 | Slave has read all relay log; waiting for more updates | NULL             |
|  2 | system user |           | NULL | Connect | 16880 | Waiting for master to send event                       | NULL             |
| 10 | root        | localhost | NULL | Query   |     0 | starting                                               | show processlist |
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)


mysql> quit
Bye
.....
[root@server7 ~]#