1.環(huán)境和版本
服務(wù)器：centos
mysql版本號：mysql-5.7.26
- mysql使用端口號
| 節(jié)點(diǎn) | 端口 |
|---|---|
| master | 3306 |
| slave | 3307 |
| slave | 3308 |
- 備注：外網(wǎng)連接需更改firewall防火墻配置
sudo firewall-cmd --zone=public --permanent --add-service=mysql
sudo systemctl restart firewalld
2.步驟
2.1安裝mysql和相關(guān)軟件
wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -c mysql
cd mysql
2.2配置
mkdir -p {conf,data,sock,logs}
- conf目錄新建
touch {3306.cnf寡喝，3307.cnf , 3308.cnf}
- logs目錄
touch {3306_err.log钥屈，3307_err.log忍宋，3308_err.log}
- data目錄
touch {3306, 3307, 3308}
主庫
vim conf/3306.cnf
[client]
default-character-set=utf8 #客戶端連接編碼
[mysql]
auto-rehash #客戶端tab補(bǔ)全
default-character-set=utf8 #編碼
[mysqld]
user=root #運(yùn)行賬戶
port=3306 #定義端口
log-bin=mysql-bin #開啟二進(jìn)制日志
server-id=001 #定義服務(wù)ID
max_allowed_packet=50M ###最大
wait_timeout=3600 ###等待超時默認(rèn)s
interactive_timeout=3600 ###活動超時
innodb_buffer_pool_size = 128M
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
basedir=/root/mysql/mysql #MYSQL根目錄
datadir=/root/mysql/data/3306 #MYSQL數(shù)據(jù)存放目錄
socket=/root/mysql/sock/3306.sock #套接字
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8 #定義數(shù)據(jù)庫默認(rèn)字符 server\collation
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
lower_case_table_names = 0 #對表的大小寫不敏感
[mysqld_safe]
log-error=/root/mysql/logs/3306_err.log #錯誤日志
pid-file=/root/mysql/data/3306/mysqld.pid #pid,多協(xié)議通信 tcp/sock
#啟動
./bin/mysqld --defaults-file=/root/mysql/conf/3306.cnf --initialize-insecure
#設(shè)置密碼
./bin/mysqladmin -u root password "123456" --socket=/root/mysql/sock/3306.sock
#登錄mysql
./bin/mysql -u root -p --socket=/root/mysql/sock/3306.sock
#查看master狀態(tài)翩活，并記下FILE及Position的值
show master status;
FILE及Position的值配置從庫需要
新建一個復(fù)制帳戶田绑，用于從庫復(fù)制binlog使用：
create user 'slavedb'@'%' identified by '123456';
GRANT REPLICATION SLAVE ON *.* TO 'slavedb'@'%';
從庫
vim conf/3307.cnf
#參照主庫配置修改3306端口為3307
./bin/mysqladmin -u root password "123456" --socket=/root/mysql/sock/3307.sock
#登錄
./bin/mysql -u root -p --socket=/root/mysql/sock/3307.sock
#停止
stop slave;
#指向主庫，最后兩個參數(shù)為file和position
change master to master_host ='127.0.0.1',master_port=3306,master_user='slavedb',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=861;
#啟動
start slave;
#查看
show slave status\G;
mysql-proxy
- 端口 4040
安裝mysql-proxy
yum install mysql-proxy
#配置文件
vim /etc/mysql-proxy.cnf
[mysql-proxy]
user=root #運(yùn)行mysql-proxy用戶
admin-username=root #主從mysql共有的用戶
admin-password=123456 #用戶的密碼
proxy-address=0.0.0.0:4040 #mysql-proxy運(yùn)行ip和端口蛔趴，不加端口宙拉，默認(rèn)4040
proxy-read-only-backend-addresses=localhost:3307 #指定后端從slave讀取數(shù)據(jù)
proxy-backend-addresses=localhost:3306 #指定后端主master寫入數(shù)據(jù)
admin-lua-script=/usr/lib64/mysql-proxy/lua/admin.lua #指定管理腳本
log-file=/root/mysql-proxy/logs/mysql-proxy.log #日志位置
log-level=info #定義log日志級別，由高到低分別有(error|warning|info|message|debug)
daemon=true
keepalive=true
chmod 660 /etc/mysql-proxy.cnf
mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
