生产环境只有一台服务器,所以才这么做的
1.停止mysql
进入d:\BtSoft\mysql
复制二个MySQL5.7
改为为MySQL5.7S1 MySQL5.7S2
2.S1和S2中配置 my.ini 文件
S1配置
[client]
default-character-set=utf8
[mysqld]
character_set_server=utf8
port=3307
basedir ="D:/data_center/BtSoft/mysql/MySQL5.7S1/"
datadir ="D:/data_center/BtSoft/mysql/MySQL5.7S1/data/"
tmpdir ="D:/data_center/BtSoft/mysql/MySQL5.7S1/data/"
socket ="D:/data_center/BtSoft/mysql/MySQL5.7S1/data/mysql.sock"
log-error="D:/data_center/BtSoft/mysql/MySQL5.7S1/data/mysql_error.log"
log-bin=mysql-bin
binlog_format=mixed
server_id = 2
#skip-locking
max_connections = 1000
table_open_cache = 2048
query_cache_size = 384M
tmp_table_size = 2048M
thread_cache_size = 256
binlog_cache_size = 256K
thread_stack = 512K
read_buffer_size = 4096K
query_cache_type = 1
max_heap_table_size = 2048M
key_buffer_size = 1024M
expire_logs_days = 10
innodb_data_home_dir="D:/data_center/BtSoft/mysql/MySQL5.7S1/data/"
innodb_flush_log_at_trx_commit =1
innodb_log_buffer_size = 128M
innodb_buffer_pool_size = 4096M
innodb_log_file_size=10M
innodb_thread_concurrency=16
innodb-autoextend-increment=1000
join_buffer_size = 8192K
sort_buffer_size = 4096K
read_rnd_buffer_size = 2048K
max_allowed_packet = 1024M
explicit_defaults_for_timestamp=true
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
#STRICT_TRANS_TABLES,
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_lock_wait_timeout = 500
wait_timeout=2880000
interactive_timeout = 2880000
S2配置
[client]
default-character-set=utf8
[mysqld]
character_set_server=utf8
port=3308
basedir ="D:/data_center/BtSoft/mysql/MySQL5.7S2/"
datadir ="D:/data_center/BtSoft/mysql/MySQL5.7S2/data/"
tmpdir ="D:/data_center/BtSoft/mysql/MySQL5.7S2/data/"
socket ="D:/data_center/BtSoft/mysql/MySQL5.7S2/data/mysql.sock"
log-error="D:/data_center/BtSoft/mysql/MySQL5.7S2/data/mysql_error.log"
log-bin=mysql-bin
binlog_format=mixed
server_id = 3
#skip-locking
max_connections = 1000
table_open_cache = 2048
query_cache_size = 384M
tmp_table_size = 2048M
thread_cache_size = 256
binlog_cache_size = 256K
thread_stack = 512K
read_buffer_size = 4096K
query_cache_type = 1
max_heap_table_size = 2048M
key_buffer_size = 1024M
expire_logs_days = 10
innodb_data_home_dir="D:/data_center/BtSoft/mysql/MySQL5.7S2/data/"
innodb_flush_log_at_trx_commit =1
innodb_log_buffer_size = 128M
innodb_buffer_pool_size = 4096M
innodb_log_file_size=10M
innodb_thread_concurrency=16
innodb-autoextend-increment=1000
join_buffer_size = 8192K
sort_buffer_size = 4096K
read_rnd_buffer_size = 2048K
max_allowed_packet = 1024M
explicit_defaults_for_timestamp=true
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
#STRICT_TRANS_TABLES,
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_lock_wait_timeout = 500
wait_timeout=2880000
interactive_timeout = 2880000
3.删除MySQL5.7S1 MySQL5.7S2中DATA目录下auto.cnf文件,否则启动不成功
4.启动主数据库并设置只读
设置只读模式(此中模式连super都不能进行写)
mysql> show global variables like "%read_only%";
mysql> flush tables with read lock;
mysql> set global read_only=1;
mysql> show global variables like "%read_only%";
5.配置主从
创建同步账号
mysql> CREATE USER 'repl'@'123.57.44.85' IDENTIFIED BY 'slavepass';#创建用户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'123.57.44.85';#分配权限
mysql>flush privileges; #刷新权限
这步省略了,直接使用root账号
查看master状态,记录位置 mysql-bin.000740 154
show master status;
在2个从库配置同步命令
stop slave;
CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_PORT=3306,MASTER_USER='root',MASTER_PASSWORD='root',MASTER_LOG_FILE='mysql-bin.000740',MASTER_LOG_POS=154;
#启动slave进程
start slave;
查看slave的状态,如果下面两项值为YES,则表示配置正确
show slave status
6.解除主库只读模式
mysql> unlock tables;
mysql> set global read_only=0;
7.设置环境变量,根据 MySQL 所在目录添加至系统环境变量
先把path中mysql的环境变量去掉,一会再加回来 BtSoft\mysql\MySQL5.7\bin;先去掉
8.以管理员方式运行,分别进入 MySQL5.7S1和MySQL5.7S2 的 bin 目录
mysqld install MySQLS1-3307 --defaults-file=D:\data_center\BtSoft\mysql\MySQL5.7S1\my.ini
mysqld install MySQLS2-3308 --defaults-file=D:\data_center\BtSoft\mysql\MySQL5.7S2\my.ini
9.有错误时再修改注册表
HKEY_LOCAL_MACHINE-->SYSTEM-->CurrentControlSet-->Services
将MySQLS1下的ImagePath改成"D:\MySql\MySQL Server 5.7S1\bin\mysqld.exe" --defaults-file=D:\data_center\BtSoft\mysql\MySQL5.7\my.ini mysqls1-3307
参考: "D:\data_center\BtSoft\mysql\MySQL5.7\bin\mysqld.exe" --defaults-file=D:\data_center\BtSoft\mysql\MySQL5.7\my.ini MySQL
修改
"D:\data_center\BtSoft\mysql\MySQL5.7S1\bin\mysqld.exe" --defaults-file=D:\data_center\BtSoft\mysql\MySQL5.7S1\my.ini mysqls1-3307
"D:\data_center\BtSoft\mysql\MySQL5.7S2\bin\mysqld.exe" --defaults-file=D:\data_center\BtSoft\mysql\MySQL5.7S2\my.ini mysqls2-3308