配置MySQL主從復制和讀寫分離

實驗環(huán)境

安裝配置MySQL數(shù)據(jù)庫

1.使用yum安裝mysql和mysql-server

yum install -y mariadb mariadb-server

2.啟動mysql服務

systemctl start mariadb
systemctl enable mariadb

3.查看啟動狀態(tài)

systemctl status mariadb
netstat -anpt | grep "mysql" --color

4.允許3306端口通過防火墻

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

5.設置MySQL密碼

mysql_secure_installation

6.在mysql-master上創(chuàng)建數(shù)據(jù)庫

使用root用戶登錄MySQL

mysql -uroot -p123456

創(chuàng)建數(shù)據(jù)庫并添加數(shù)據(jù)

create database db_test;
show databases;
use db_test;

create table if not exists user_info(
    username varchar(16) not null,
    password varchar(32) not null,
    realname varchar(16) default '',
    primary key (username)
)default charset=utf8;
show tables;

insert into user_info(username, password, realname) values
('10001', '123456', '小明'),
('10002', '123456', '小紅'),
('10003', '123456', '小王'),
('10004', '123456', '小張'),
('10005', '123456', '小李');

select * from user_info where 1;

在mysql-master上授權數(shù)據(jù)庫訪問權限

GRANT all ON db_test.* TO 'admin'@'%' identified BY '123456';
flush privileges;

在mysql-slave、appserver上登錄mysql-master數(shù)據(jù)庫

mysql -h 192.168.204.201 -uroot -p123456

在mysql-master上撤銷數(shù)據(jù)庫訪問權限

REVOKE all ON db_test.* FROM 'admin'@'%';
flush privileges;

配置master和slave兩臺mysql服務器的主從復制

1.在master數(shù)據(jù)庫上啟用binlog日志，建立從庫賬號rep

查看binlog日志狀態(tài)

show variables like 'log_bin';

更改my.cnf配置文件

cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

重啟MySQL，查看binlog日志

systemctl restart mariadb
mysql -uroot -p123456 -e "show variables like 'log_bin';"
mysql -uroot -p123456 -e "show master status;"

記住此處File和Position的值

建立從庫賬號

grant replication slave on *.* to rep@'192.168.204.202' identified by '123456';
show grants for rep@'192.168.204.%';

2.在master數(shù)據(jù)庫上備份現(xiàn)有數(shù)據(jù)庫

對master數(shù)據(jù)庫鎖表

flush tables with read lock;

備份master數(shù)據(jù)庫

mysqldump -uroot -p123456 --all-databases | gzip > /root/database_`date '+%Y-%m-%d'`.sql.gz

將備份文件拷貝至slave

scp database_*.sql.gz root@192.168.204.202:/root

3. 配置slave數(shù)據(jù)庫，在slave上恢復數(shù)據(jù)庫

配置slave數(shù)據(jù)庫server-id，關閉binlog日志

cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

# vim /etc/my.cnf

#log-bin=mysql-bin
#binlog_format=mixed
server-id       = 2

4.重啟slave的mysql

重啟mysql服務

systemctl restart mariadb

查看log_bin和server_id的值

show variables like 'log_bin';
show variables like 'server_id';

5.將數(shù)據(jù)恢復至slave

gzip -d /root/database_*.sql.gz
mysql -uroot -p123456 < /root/database_*.sql
mysql -uroot -p123456 -e "show databases;"

6.在slave數(shù)據(jù)庫上配置復制參數(shù)

在slave上配置復制參數(shù)
將MASTER_LOG_FILE和MASTER_LOG_POS的值替換成上述master上查詢的值

change master to
MASTER_HOST='192.168.204.201',
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=245;

在slave上配置啟用復制

start slave;

在slave上查看復制狀態(tài)

show slave status G;

兩個均為Yes即可

重啟master和slave的mysql服務

systemctl restart mariadb

在master上為數(shù)據(jù)庫db_test增加記錄，在slave查看同步情況

-- mysql-master
insert into db_test.user_info (username, password, realname) values ('20001', '123456', 'Tom');
select * from db_test.user_info where 1;

-- mysql-slave
select * from db_test.user_info where 1;

已經實現(xiàn)了主從復制

在appserver上配置mysql讀寫分離

1.在appserver上安裝mysql-proxy

從https://downloads.mysql.com/archives/proxy/下載mysql-proxy

cd ~
wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-sles11-x86-64bit.tar.gz
tar -xzvf mysql-proxy-0.8.5-linux-sles11-x86-64bit.tar.gz
cp -r mysql-proxy-0.8.5-linux-sles11-x86-64bit /usr/local/mysql-proxy

2.在appserver上配置mysql-proxy

創(chuàng)建主配置文件

cd /usr/local/mysql-proxy
mkdir lua        #創(chuàng)建腳本存放目錄
mkdir logs       #創(chuàng)建日志目錄
cp share/doc/mysql-proxy/rw-splitting.lua ./lua/  #復制讀寫分離配置文件
cp share/doc/mysql-proxy/admin-sql.lua ./lua/     #復制管理腳本
vim /etc/mysql-proxy.cnf     #創(chuàng)建配置文件

主配置文件內容

使用前，請去掉注釋

#vim /etc/mysql-proxy.cnf

[mysql-proxy]
user=root #運行mysql-proxy用戶
admin-username=myproxy #主從mysql共有的用戶
admin-password=123456 #用戶的密碼
proxy-address=127.0.0.1:3306 #mysql-proxy運行ip和端口，不加端口默認4040
proxy-read-only-backend-addresses=192.168.204.202 #指定后端從slave讀取數(shù)據(jù)
proxy-backend-addresses=192.168.204.201 #指定后端master寫入數(shù)據(jù)
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua #指定讀寫分離配置文件位置
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql/lua #指定管理腳本
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log #日志位置
log-level=info #定義log日志級別，由高到低分別(error|warning|info|message|debug)

修改權限

chmod 660 /etc/mysql-proxy.cnf

3.在appserver上修改讀寫分離配置文件

vim /usr/local/mysql-proxy/lua/rw-splitting.lua

修改以下內容

--- config
--
-- connection pool
if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
                min_idle_connections = 1,  -- 默認超過4個連接數(shù)時才開始讀寫分離
                max_idle_connections = 1,  -- 默認為8

                is_debug = false
        }
end

4.在appserver上啟動mysql-proxy

啟動

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf --daemon

查看進程

netstat -anpt | grep 3306

5.在mysql-master和mysql-slave上分別給myproxy授權

在mysql-master和mysql-slave上授權給mysql-proxy

grant all on *.* to 'myproxy'@'192.168.204.%' identified by '123456';
flush privileges;

6.在appserver上連接mysql-proxy，測試讀寫分離

在appserver上通過mysql-proxy操作數(shù)據(jù)庫

mysql -h 127.0.0.1 -umyproxy -p123456 -e "select * from db_test.user_info where 1;"
mysql -h 127.0.0.1 -umyproxy -p123456 -e "insert into db_test.user_info (username, password, realname) values ('30001', '123456', 'Jack');"
mysql -h 127.0.0.1 -umyproxy -p123456 -e "select * from db_test.user_info where 1;"

在mysql-master上查詢

mysql -uroot -p123456 -e "select * from db_test.user_info where 1;"

在mysql-master上查詢

mysql -uroot -p123456 -e "select * from db_test.user_info where 1;"

經驗證，已實現(xiàn)讀寫分離

鏈接：https://www.cnblogs.com/connect/p/mysql-master-slave-copy-and-read-write-separation.html