ecstore多级部署之配置mysql主从(多台数据库进行相关配置详解)
1、配置主服务器:
vim /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
#datadir =/data/mysql //basedir=/data/mysql
skip-external-locking //datadir=/data/mysql/data
key_buffer_size = 256M
max_allowed_packet = 128M
table_open_cache = 128
sort_buffer_size = 16M
net_buffer_length = 32K
read_buffer_size = 128M
read_rnd_buffer_size = 256M
myisam_sort_buffer_size = 256M
expire_logs_days = 1
max_connections=3000
max_user_connections=3000
skip-name-resolve
binlog-do-db=ecstore
binlog-ignore-db=mysql
binlog-ignore-db=test
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
2、创建数据库ecstore
CREATE DATABASE ecstore;
导入信息
GRANT REPLICATION SLAVE ON *.* TO 'root'@'slaveIP' IDENTIFIED BY 'yinduo123'
查看状态
SHOW MASTER STATUS;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 1033 | ecstore | mysql,test | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
表示配置成功
3、设置从服务器:vim /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
#datadir =/data/mysql //basedir=/data/mysql
skip-external-locking //datadir=/data/mysql/data
key_buffer_size = 256M
max_allowed_packet = 128M
table_open_cache = 128
sort_buffer_size = 16M
net_buffer_length = 32K
read_buffer_size = 128M
read_rnd_buffer_size = 256M
myisam_sort_buffer_size = 256M
expire_logs_days = 1
max_connections=3000
max_user_connections=3000
skip-name-resolve
binlog-do-db=ecstore
binlog-ignore-db=mysql
binlog-ignore-db=test
log-bin=mysql-bin
binlog_format=mixed
server-id = 2
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
进入数据库,先关闭slave
STOP SLAVE;
导入信息
CHANGE MASTER TO MASTER_HOST='主服务IP', MASTER_USER='用户名', MASTER_PASSWORD='密码', MASTER_LOG_FILE='mysql-bin.000115', MASTER_LOG_POS=1033
# mysql-bin.000002是在主服务器mysql中通过 show master status; 查询到的 字段file的值
# 1033是对应 Position的值
开启SLAVE
START SLAVE;
查看同步情况
SHOW SLAVE STATUS\G;
4.最后修改ecstore相关配置
vim config/config.php
// 数据库配置
define('DB_USER', 'ecstore'); // 数据库用户名
define('DB_PASSWORD', 'ecstore123'); // 数据库密码
define('DB_NAME', 'ecstore'); // 数据库名
define('DB_HOST', '127.0.0.1'); // 数据库HOST
define('DB_PREFIX', 'sdb_');
# define('DB_PCONNECT', 1); // 是否启用数据库持续连接?
// 数据库集群.
define('DB_SLAVE_NAME', DB_NAME);
define('DB_SLAVE_USER', DB_USER);
define('DB_SLAVE_PASSWORD', DB_PASSWORD);
define('DB_SLAVE_HOST', DB_HOST);
可参考下图进行配置:
ps:这里的host为主sql的内网IP
ps:如果需要深度优化,可参考文章:https://jinjiajin.net/107.html