配置MySQL主從復(fù)制和讀寫分離

實(shí)驗(yàn)環(huán)境

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

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

yum install -y mariadb mariadb-server

2.啟動(dòng)mysql服務(wù)

systemctl start mariadb
systemctl enable mariadb

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

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

4.允許3306端口通過(guò)防火墻

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

5.設(shè)置MySQL密碼

mysql_secure_installation

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

使用root用戶登錄MySQL

mysql -uroot -p123456

創(chuàng)建數(shù)據(jù)庫(kù)并添加數(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上授權(quán)數(shù)據(jù)庫(kù)訪問(wèn)權(quán)限

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

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

mysql -h 192.168.204.201 -uroot -p123456

在mysql-master上撤銷數(shù)據(jù)庫(kù)訪問(wèn)權(quán)限

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

配置master和slave兩臺(tái)mysql服務(wù)器的主從復(fù)制

1.在master數(shù)據(jù)庫(kù)上啟用binlog日志，建立從庫(kù)賬號(hào)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的值

建立從庫(kù)賬號(hào)

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

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

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

flush tables with read lock;

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

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ù)庫(kù)，在slave上恢復(fù)數(shù)據(jù)庫(kù)

配置slave數(shù)據(jù)庫(kù)server-id，關(guān)閉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服務(wù)

systemctl restart mariadb

查看log_bin和server_id的值

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

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

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

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

在slave上配置復(fù)制參數(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上配置啟用復(fù)制

start slave;

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

show slave status G;

兩個(gè)均為Yes即可

重啟master和slave的mysql服務(wù)

systemctl restart mariadb

在master上為數(shù)據(jù)庫(kù)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;

已經(jīng)實(shí)現(xiàn)了主從復(fù)制

在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/  #復(fù)制讀寫分離配置文件
cp share/doc/mysql-proxy/admin-sql.lua ./lua/     #復(fù)制管理腳本
vim /etc/mysql-proxy.cnf     #創(chuàng)建配置文件

主配置文件內(nèi)容

使用前，請(qǐng)去掉注釋

#vim /etc/mysql-proxy.cnf

[mysql-proxy]
user=root #運(yùn)行mysql-proxy用戶
admin-username=myproxy #主從mysql共有的用戶
admin-password=123456 #用戶的密碼
proxy-address=127.0.0.1:3306 #mysql-proxy運(yùn)行ip和端口，不加端口默認(rèn)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日志級(jí)別，由高到低分別(error|warning|info|message|debug)

修改權(quán)限

chmod 660 /etc/mysql-proxy.cnf

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

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

修改以下內(nèi)容

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

                is_debug = false
        }
end

4.在appserver上啟動(dòng)mysql-proxy

啟動(dòng)

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

查看進(jìn)程

netstat -anpt | grep 3306

5.在mysql-master和mysql-slave上分別給myproxy授權(quán)

在mysql-master和mysql-slave上授權(quán)給mysql-proxy

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

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

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

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;"

經(jīng)驗(yàn)證，已實(shí)現(xiàn)讀寫分離

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