实现在同一台linux主机上mysql主从复制与读写分离

环境情况：由于资源有限，仅在一台CentOS release 6.6上实现M-S主从复制与读写分离

一、mysql安装与配置

具体安装过程建议参考我的上篇一博客文章

二、mysql主从复制

主从服务器场景如下

主（m）   ：172.30.204.111:3307

从1（s1）：172.30.204.111:3308

从2（s2）：172.30.204.111:3309

[root@master dingmingyi]# netstat-anlpt|grep mysql

tcp       0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      19411/mysqld       

tcp       0      0 0.0.0.0:3308                0.0.0.0:*                   LISTEN     19734/mysqld       

tcp       0      0 0.0.0.0:3309                0.0.0.0:*                   LISTEN      20202/mysqld

 

2.1主服务器操作：

[root@master dingmingyi]# cat /usr/local/mysql-m/etc/my.cnf [mysqld] basedir=/usr/local/mysql-m datadir=/opt/database-m socket=/var/run/mysql-m/mysql-m.sock pid-file=/var/run/mysql-m/mysql-m.pid port=3307 user=mysql server-id=1                           log-bin=mysql-bin binlog_ignore_db=mysql character_set_server=utf8 [mysqld_safe] log-error=/var/log/mysql-m/mysql--error.log [mysql] socket = /var/run/mysql-m/mysql-m.sock default-character-set=utf8

只要在my.cnf里面添加内容都要重新启动服务#service mysql-m restart

[root@master dingmingyi]#/usr/local/mysql-m/bin/mysqladmin -uroot password '321321'  -S /var/run/mysql-m/mysql-m.sock               #为M-mysql设置密码；

[root@master dingmingyi]# /usr/local/mysql-m/bin/mysql-uroot -p321321 -S /var/run/mysql-m/mysql-m.sock

 

授权给从服务器

mysql> grant replication slave on *.* tos1@localhost identified by '123123';

mysql> grant replication slave on *.* tos2@localhost identified by '123123';

mysql>flush privileges;

 

查询主数据库状态

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

记录file和position的值，在配置从服务器时需要用到

 

2.2从服务器操作

修改从服务器配置文件/usr/local/mysql-s1/etc/my.cnf

将server-id=10 ，确保此id与主服务器不同                                                     #设置多个从服务器必须与M-S和S-S都不同

同样在[mysql]下增加default-character-set=utf8 ，在[mysqld]下增加character_set_server=utf8

重启服务

 

登陆从服务器

/usr/local/mysql-s1/bin/mysql -uroot -p -S/var/run/mysql-s1/mysql-s1.sock              #由于没设密码，可以直接回车进入

 

执行同步语句

>change master to  master_host='localhost',  master_user='s1', master_password='123123', master_log_file='mysql-bin.000001', master_log_pos=670, master_port=3307;                                #一定要指定相应的端口号，不然在查看状态时会出错 >start slave;

 

mysql> show slave status\G *************************** 1. row ***************************                   Slave_IO_State: Waiting for master to send event                        Master_Host: localhost                        Master_User: s1    &n