一台服务器多实例 MySQL 做主从复制
2021/4/10 2:28:42
本文主要是介绍一台服务器多实例 MySQL 做主从复制,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
在一台服务器上开两个端口的mysql(3306、3307),做成主从复制环境
1)安装mysql(安装过程这里就不做过多介绍) 参考:http://www.cnblogs.com/kevingrace/p/6109679.html 本文在一台服务器上做主从实验 主库:172.29.16.24:3306 从库:172.29.16.24:3307 主从库的安装目录分别为/usr/local/mysql3306、/usr/local/mysql3307 主从库的数据目录分别为/data/mysql3306、/data/mysql3307 主从库的登录密码都为123456 两个实例的my.cnf里不一样的配置部分 端口不一样!另外:server-id一定不能一样,最好用端口号来标明server-id,一个是3306,一个是3307 其他内容配置一样,主库一定要开启binlog日志功能,从库可以开启,也可以不开启) [root@radius01 ~]# cat /usr/local/mysql3306/my.cnf |grep mysql3306 socket = /usr/local/mysql3306/var/mysql.sock socket = /usr/local/mysql3306/var/mysql.sock basedir = /usr/local/mysql3306/ datadir = /data/mysql3306/data pid-file = /data/mysql3306/data/mysql.pid log_error = /data/mysql3306/data/mysql-error.log slow_query_log_file = /data/mysql3306/data/mysql-slow.log [root@radius01 ~]# cat /usr/local/mysql3307/my.cnf |grep mysql3307 socket = /usr/local/mysql3307/var/mysql.sock socket = /usr/local/mysql3307/var/mysql.sock basedir = /usr/local/mysql3307/ datadir = /data/mysql3307/data pid-file = /data/mysql3307/data/mysql.pid log_error = /data/mysql3307/data/mysql-error.log slow_query_log_file = /data/mysql3307/data/mysql-slow.log 注意分别授权 [root@radius01 ~]# chown -R mysql.mysql /usr/local/mysql3306 [root@radius01 ~]# chown -R mysql.mysql /usr/local/mysql3307 [root@radius01 ~]# chown -R mysql.mysql /data/mysql3306/ [root@radius01 ~]# chown -R mysql.mysql /data/mysql3307 启动主从库 [root@radius01 ~]# nohup /usr/local/mysql3306/bin/mysqld_safe --datadir=/data/mysql3306/data --pid-file=/data/mysql3306/data/mysql.pid & [root@radius01 ~]# nohup /usr/local/mysql3307/bin/mysqld_safe --datadir=/data/mysql3307/data --pid-file=/data/mysql3307/data/mysql.pid & [root@radius01 ~]# lsof -i:3306 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 28617 mysql 19u IPv4 838860 0t0 TCP *:mysql (LISTEN) [root@radius01 ~]# lsof -i:3307 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 29538 mysql 19u IPv4 839401 0t0 TCP *:opsession-prxy (LISTEN) 注意,当一台服务器上安装了多个实例的mysql的时候,直接登录mysql的时候,记住要在登录命令中跟上对应的sock路径,如下: [root@radius01 ~]# /usr/local/mysql3306/bin/mysql -p123456 -S /usr/local/mysql3306/var/mysql.sock ...... mysql> select @@port; +--------+ | @@port | +--------+ | 3306 | +--------+ 1 row in set (0.00 sec) [root@radius01 ~]# /usr/local/mysql3307/bin/mysql -p123456 -S /usr/local/mysql3307/var/mysql.sock ...... mysql> select @@port; +--------+ | @@port | +--------+ | 3307 | +--------+ 1 row in set (0.00 sec) ------------------------------------------------------------------------------------------------------------------------ 如果不跟上对应的sock路径,直接登录的话会报错: [root@radius01 ~]# /usr/local/mysql3306/bin/mysql -p123456 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' 这样,如果你如下做软链接: [root@radius01 ~]# ln -s /usr/local/mysql3306/var/mysql.sock /var/lib/mysql/mysql.sock 或者 [root@radius01 ~]# ln -s /usr/local/mysql3307/var/mysql.sock /var/lib/mysql/mysql.sock 那么这样操作之后,无论是登录3306端口的mysql,还是登录3307端口的mysql,里面的操作都是一样的,即这样就分不清两个端口的mysql实例了! 所以还是在登录各个端口的mysql实例时要跟上对应的sock路径! 2)部署主从复制环境 先在主库上操作: [root@radius01 ~]# /usr/local/mysql3306/bin/mysql -p123456 -S /usr/local/mysql3306/var/mysql.sock ...... mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.29.16.%' IDENTIFIED BY 'mycatms'; mysql> flush privileges; mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000015 | 199 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 接着在从库(即33077端口)上设置主从复制 先在从库上验证下是否能使用授予的权限连接主库 [root@radius01 ~]# /usr/local/mysql3307/bin/mysql -u repl -h 172.29.16.24 -pmycatms -S /usr/local/mysql3306/var/mysql.sock ..... mysql> select @@port; +--------+ | @@port | +--------+ | 3306 | +--------+ 1 row in set (0.00 sec) 然后进行主从复制设置 [root@radius01 ~]# /usr/local/mysql3307/bin/mysql -p123456 -S /usr/local/mysql3307/var/mysql.sock ....... mysql> select @@port; +--------+ | @@port | +--------+ | 3307 | +--------+ 1 row in set (0.00 sec) mysql> stop slave; mysql> reset slave; mysql> change master to master_user='repl', master_password='mycatms', master_host='172.29.16.24',master_port=3306, master_log_file='mysql-bin.000015',master_log_pos=199; mysql> start slave; mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.29.16.24 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000015 Read_Master_Log_Pos: 199 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000015 Slave_IO_Running: Yes Slave_SQL_Running: Yes ....... ....... ------------------------------------------------------------------------------------------------------------------------------------- 如果出现下面报错: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. 原因分析: mysql 5.6的复制引入了uuid的概念,各个复制结构中的server_uuid得保证不一样,但是查看到直接copy data文件夹后server_uuid是相同的,show variables like '%server_uuid%'; 也就是说: 我的实验环境中的3306端口的mysql实例和3307端口的mysql实例的uuid是一样的导致的,因为我是直接复制的data数据目录。如下查看: [root@radius01 ~]# /usr/local/mysql3306/bin/mysql -p123456 -S /usr/local/mysql3306/var/mysql.sock ......... mysql> show variables like '%server_uuid%'; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | 5509fe69-96b6-11e7-aef5-aaafa07a2e23 | +---------------+--------------------------------------+ 1 row in set (0.00 sec) [root@radius01 ~]# /usr/local/mysql3307/bin/mysql -p123456 -S /usr/local/mysql3307/var/mysql.sock ...... mysql> show variables like '%server_uuid%'; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | 5509fe69-96b6-11e7-aef5-aaafa07a2e23 | +---------------+--------------------------------------+ 1 row in set (0.00 sec) 解决办法: 找到3306端口和3307端口的data文件夹下的auto.cnf文件,直接删除掉,然后重启各自的mysql即可!重启后,可以再次生成auto.conf文件(重新生成后的uuid就不一样了) [root@radius01 ~]# rm -rf /data/mysql3306/data/auto.cnf [root@radius01 ~]# rm -rf /data/mysql3307/data/auto.cnf [root@radius01 ~]# nohup /usr/local/mysql3306/bin/mysqld_safe --datadir=/data/mysql3306/data --pid-file=/data/mysql3306/data/mysql.pid & [root@radius01 ~]# nohup /usr/local/mysql3307/bin/mysqld_safe --datadir=/data/mysql3307/data --pid-file=/data/mysql3307/data/mysql.pid & [root@radius01 ~]# cat /data/mysql3306/data/auto.cnf [auto] server-uuid=f6a726d2-96fd-11e7-b0c8-aaafa07a2e23 [root@radius01 ~]# cat /data/mysql3307/data/auto.cnf [auto] server-uuid=fc6ee68c-96fd-11e7-b0c8-aaafa07a2e23 ------------------------------------------------------------------------------------------------------------------------------------- 数据同步测试: 在主库里写入新数据 [root@radius01 ~]# /usr/local/mysql3306/bin/mysql -p123456 -S /usr/local/mysql3306/var/mysql.sock mysql> create database wangshibo; mysql> use wangshibo; mysql> create table tehui( -> id int not null primary key, -> name varchar(10)); mysql> insert into tehui values(1,"huanhuan"); mysql> insert into tehui values(11,"meimei"); mysql> select * from tehui; +----+----------+ | id | name | +----+----------+ | 1 | huanhuan | | 11 | meimei | +----+----------+ 2 rows in set (0.00 sec) 在从库查看是否已同步 [root@radius01 ~]# /usr/local/mysql3307/bin/mysql -p123456 -S /usr/local/mysql3307/var/mysql.sock mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | wangshibo | +--------------------+ 5 rows in set (0.00 sec) mysql> use wangshibo; mysql> show tables; +---------------------+ | Tables_in_wangshibo | +---------------------+ | tehui | +---------------------+ 1 row in set (0.00 sec) mysql> select * from tehui; +----+----------+ | id | name | +----+----------+ | 1 | huanhuan | | 11 | meimei | +----+----------+ 2 rows in set (0.00 sec)
这篇关于一台服务器多实例 MySQL 做主从复制的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-12-25如何部署MySQL集群资料:新手入门教程
- 2024-12-24MySQL集群部署资料:新手入门教程
- 2024-12-24MySQL集群资料详解:新手入门教程
- 2024-12-24MySQL集群部署入门教程
- 2024-12-24部署MySQL集群学习:新手入门教程
- 2024-12-24部署MySQL集群入门:一步一步搭建指南
- 2024-12-07MySQL读写分离入门:轻松掌握数据库读写分离技术
- 2024-12-07MySQL读写分离入门教程
- 2024-12-07MySQL分库分表入门详解
- 2024-12-07MySQL分库分表入门指南