master数据库 /etc/my.cnf 配置
[client]
default-character-set=utf8mb4
port = 3306
socket = /usr/local/mysqld/mysqld.sock #用于本地连接的socket套接字
[mysql]
#这个配置段设置启动MySQL服务的条件;在这种情况下,no-auto-rehash确保这个服务启动得比较快。
no-auto-rehash
[mysqld]
#user = mysql
port = 3306
basedir=/usr/local/mysql/ #使用该目录作为根目录(安装目录)
datadir=/usr/local/mysql/data/ #数据文件存放的目录
tmpdir = /usr/local/mysqld/tmp/ #MySQL存放临时文件的目录
socket =/usr/local/mysqld/mysqld.sock #为MySQL客户端程序和服务器之间的本地通讯指定一个套接字文件
explicit_defaults_for_timestamp=true #明确时间戳默认null方式
character_set_server=utf8mb4 #服务端默认编码(数据库级别)
collation_server =utf8mb4_bin #服务端默认的比对规则,排序规则
default-storage-engine = InnoDB #默认存储引擎
server-id=1 #设置server-id
log-bin=mysql-bin #开启二进制日志
binlog_format=mixed #复制模式
# 不同步哪些数据库
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema
# 只同步哪些数据库,除此之外,其他不同步
binlog-do-db = game
#Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-warnings = 1 #将警告打印输出到错误 log 文件
[mysqld_safe]
pid-file = /usr/local/mysqld/mysqld.pid
socket = /usr/local/mysqld/mysqld.sock
slow_query_log = 1 #开启慢查询
long-query-time = 10 #漫查询阀值
log_long_format #在慢速日志中记录更多的信息
slow_query_log_file = /usr/local/mysqld/log/slow_sql.log #慢查询路径
general_log = 0 #将所有到达MySQL Server的SQL语句记录下来,默认关闭
general_log_file = /usr/local/mysqld/log/mysql_sql.log
log-error = /usr/local/mysqld/log/mysqld.err #错误日志路径
slave 数据库 /etc/my.cnf 配置
[client]
default-character-set=utf8mb4
port = 3306
socket = /usr/local/mysqld/mysqld.sock #用于本地连接的socket套接字
[mysql]
#这个配置段设置启动MySQL服务的条件;在这种情况下,no-auto-rehash确保这个服务启动得比较快。
no-auto-rehash
[mysqld]
#user = mysql
port = 3306
basedir=/usr/local/mysql/ #使用该目录作为根目录(安装目录)
datadir=/usr/local/mysql/data/ #数据文件存放的目录
tmpdir = /usr/local/mysqld/tmp/ #MySQL存放临时文件的目录
socket =/usr/local/mysqld/mysqld.sock #为MySQL客户端程序和服务器之间的本地通讯指定一个套接字文件
explicit_defaults_for_timestamp=true #明确时间戳默认null方式
character_set_server=utf8mb4 #服务端默认编码(数据库级别)
collation_server =utf8mb4_bin #服务端默认的比对规则,排序规则
default-storage-engine = InnoDB #默认存储引擎
server-id=2 #设置server-id
#Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-warnings = 1 #将警告打印输出到错误 log 文件
[mysqld_safe]
pid-file = /usr/local/mysqld/mysqld.pid
socket = /usr/local/mysqld/mysqld.sock
slow_query_log = 1 #开启慢查询
long-query-time = 10 #漫查询阀值
log_long_format #在慢速日志中记录更多的信息
slow_query_log_file = /usr/local/mysqld/log/slow_sql.log #慢查询路径
general_log = 0 #将所有到达MySQL Server的SQL语句记录下来,默认关闭
general_log_file = /usr/local/mysqld/log/mysql_sql.log
log-error = /usr/local/mysqld/log/mysqld.err #错误日志路径
master 创建授权用户
mysql>CREATE USER 'prdrepl'@'192.168.16.%' IDENTIFIED BY 'root';
mysql> use mysql;
mysql> update user set authentication_string=password('reduser') where user='reduser';
用户赋予 REPLICATION SLAVE 权限
mysql>GRANT REPLICATION SLAVE ON *.* TO 'prdrepl'@'192.168.16.%';
mysql> flush privileges;
查看用户
mysql>select user,host from mysql.user;
查看 master 状态
mysql>show master status;
//这里的 mysql-bin.000001和 Position 值 slave 配置时需要用到。
手动同步master数据
mysqldump -u root -p --all-databases --master-data > all.sql
//命令失败 设置软连接
find / -name mysql -print
ln -fs /usr/local/mysql/bin/mysqldump /usr/bin
ln -fs /usr/local/mysql/bin/mysql /usr/bin
scp all.sql root@192.168.16.14:/tmp
mysql>stop slave
mysql -uroot -p < all.sql
使 slave 与 master 建立连接
mysql> change master to
-> master_host='192.168.16.16',
-> master_user='prdrepl',
-> master_password='prdrepl',
-> master_port=3306,
-> master_log_file='mysql-bin.000001',
-> master_log_pos=623;
启动备份
mysql>start slave;
查看备份是否启动成功
mysql>show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
设置只读属性
mysql>set global read_only=1 //1只读,0读写
mysql>show global variables like "%read_only%";
mysql>flush tables with read lock;// 设定全局锁 备份失效
mysql>unlock tables; //解锁全局锁