mysql多实例及主从复制

mysql多实例及主从复制

简介：
同一服务器多实例mysql：
优点：有效利用服务器资源
缺点：当某个数据库实例并发很高或有慢SQL查询时整个实例会消耗大量的系统CPU，磁盘，I/O等资源

适用场景：
1.资金紧张的公司
2.并发访问不是特别大的业务
3.门户网站（一般从库多实例）

搭建3307端口mysql

1.解压安装

mkdir /opt/3307_mysql

cp mysql-5.6.30-linux-glibc2.5-x86_64.tar.gz /opt/3307_mysql/

cd /opt/3307_mysql/

tar zxvf mysql-5.6.30-linux-glibc2.5-x86_64.tar.gz

ln -s /opt/3307_mysql/mysql-5.6.30-linux-glibc2.5-x86_64 /opt/3307_mysql/mysql

mkdir -p /data/my3307

mkdir /data/my3307/{run,data,log,tmp}

chown -R mysql:mysql /data/my3307

chown -R mysql:mysql mysql-5.6.30-linux-glibc2.5-x86_64

2.配置修改

修改配置文件vim /data/my3307/my.cnf

basedir = /opt/3307_mysql/mysql

datadir = /data/my3307/data

port = 3307 （指定端口）

server_id = …

socket = /data/my3307/mysql.sock（多实例重点就是指定socket文件）

ln -s /data/my3307/my.cnf /opt/3307_mysql/mysql/my.cnf

注：配置初始化等操作都同单实例，登录不同实例时指定不同的socket文件

eg:mysql -uroot -h127.0.0.1 -S /data/my3307/mysql.sock

利用自己搭建的多实例mysql做主从复制

主从复制应用场景：

数据备份

读写分离，从服务器上实现负载均衡

从服务器根据业务重要性进行拆分访问

主从复制原理：

异步复制过程，打开master端的Binlog记录功能，复制过程就是slave端从master获取binlog日志，再在slave上以相同顺序执行binlog日志中所记录的各种SQL操作。

打开binlog记录功能：

[mysqld]

log-bin = /data/my3306/mysql-bin

master:

1.修改配置：

添加id号，开启binlog功能

vim /data/my3306/my.cnf

[mysqld]

server-id = 1

log-bin = /data/my3306/mysql-bin

2.重启主库：

mysqladmin -uroot -S /data/my3306/mysql.sock shut down 关闭

cd /opt/mysql

./bin/mysqld_safe --defaults-file=/data/my3306/my.cnf --user=mysql & 启动

3.登录数据库检查：

mysql -uroot -S /data/my3306/mysql.sock

mysql> show variables like ‘server_id’;

08ab8a61d6551827146eb5b15ad9e758cb59fa52

检查binlog功能是否开启。

mysql> show variables like ‘log_bin’;

ba4921dc6c869d1e57867d7e482f62212c965903

4.建立从库复制的账号rep：

mysql> grant replication slave on . to ‘rep’@’%’ identified by ‘abc123’;

67fe28ef1493010c41a40a0878199bad2c977995

mysql> flush privileges; 刷新授权表

5.实现对主数据库锁表只读（不退出当前窗口）

mysql> flush table with read lock;

mysql> show master status;

943c9bbe5822fa96641876cee1a1f262e4057b7a

从库导入全备后，继续主从复制时从这个位置开始。

导出数据库所有文件：

mysqldump -uroot -S /data/my3306/mysql.sock --master-data=2 --events -A -B | gzip > mysql_bak.$(date +%F).sql.gz

解锁：mysql> unlock tables;

–master-data

–single-transaction

–master-data[=#] 在备份导出的文件里追加二进制binlog文件的位置和名称

                    如果值等于1，就会添加一个CHANGE MASTER语句 

                    如果值等于2，就会在CHANGE MASTER语句前添加注释（不起作用了呗~） 

                    这个参数会--lock-all-tables锁表，除非你指定了--single-transaction 

                    这种情况下，锁表只会在dump开始的时候持续一小段时间,照理说 

                    在dump的时候，任何动作都会影响到binlog文件 

                    dump结束之后，选项会自动关闭锁表功能

就是主从复制在做全量备份的时候，这个选项可以自动帮我们锁表和识别binlog临界文件，就不需要我们锁表，再看临界文件编号，再执行CHANGE MASTER填写binglong位置信息到从库master.info文件中了，提高了从库部署效率吧。

从库：

编辑配置文件

vim /data/my3307/my.cnf

[mysqld]

server-id = 3

重启数据库：

mysqladmin -uroot -S /data/my3307/mysql.sock shut down

cd /opt/3307_mysql/mysql

./bin/mysqld_safe --defaults-file=/data/my3307/my.cnf --user=mysql &

检查参数：

mysql> show variables like ‘log_bin’;

mysql> show variables like ‘server_id’;

导入数据：

gzip -d mysql_bak.2016-08-31.sql.gz

mysql -S /data/my3307/mysql.sock < mysql_bak.2016-08-31.sql

登录从库，配置参数：

mysql> change master to master_host=‘172.16.1.233’,

-> master_port=3306,

-> master_user='rep',

-> master_password='abc123',

-> master_log_file='mysql-bin.000001',

-> master_log_pos=396;

开启从库：

mysql -S /data/my3307/mysql.sock -e “start slave;”

mysql -S /data/my3307/mysql.sock -e “show slave status\G;”

2a1fe7f161e18b61b7502d28b7b17a1a200f8216

检测：在主库插入数据，登录从库查看是否同步。

主备断开时，重新指定断开时的binlog文件和setion即可