mysql主从数据库-笔记

主从数据库

• 一种主备模式的数据库应用；
• 主库（master）数据与备库（slave）数据完全一致；
• 实现数据的多重备份，保证数据的安全；
• 可以在Master[InnoDB]和Slave[MyISAM]中使用不同的数据库引擎，实现读写的分离；

1.1 Master【主库】配置（默认mysql数据库已安装完毕，环境：centos7）

1.1.1 修改Master配置文件

默认位置：/etc/mysql.cnf
备份原配置文件mysql.cnf.bak，新建新的配置文件：mysql.cnf

[client]
default-character-set=utf8mb4 #客户端默认字符集

[mysqld] #服务器
port = 3306 #端口

user = mysql #提供简单用户数据
bind-address = 0.0.0.0 #当前用户可访问的主机IP, localhost. mysql用户只能在本地访问.
server-id = 1 #服务器的唯一标识

init-connect = 'SET NAMES utf8mb4' #初始化数据库链接时提供的配置信息
character-set-server = utf8mb4 #服务器基本字符集

skip-name-resolve
skip-external-locking
#skip-networking
back_log = 300

max_connections = 1000 #最大客户端连接数
max_connect_errors = 6000 #单次连接最大可能出现的错误数.
open_files_limit = 65535
table_open_cache = 128 #表的开启基础缓存. 单位是个数
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M

read_buffer_size = 2M #读缓存大小
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_type = 1
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
log_bin = master_log #日志文件名称.文件名的主体内容.mysql会自动提供后缀和文件类型.
binlog_format = mixed
expire_logs_days = 10
slow_query_log = 1
long_query_time = 1
performance_schema = 0
explicit_defaults_for_timestamp

lower_case_table_names = 1
default_storage_engine = InnoDB #默认数据库引擎
#default-storage-engine = MyISAM
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

interactive_timeout = 28800
wait_timeout = 28800

[mysqldump] #安全提供.
quick
max_allowed_packet = 16M

[myisamchk] #数据库额外辅助功能配置, 如缓存,索引缓存等.
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

配置环境中的 server-id 是 1 ，作为Mysql服务的唯一标识【自然数/整数】。

server-master的唯一标识必须 小于 server-slave的唯一标识。

主数据库开启日志功能：配置中 log_bin:master_log ，log_bin不是执行日志，是操作日志/状态日志，在DBMS中所有的sql命令，log_bin并非必要的。

1.1.2 重启mysql

service mysqld restart

1.1.3 配置Master

1.1.3.1 访问主mysql

mysql -uusername -ppassword;

1.1.3.2 创建用户

在mysql数据库中，为不存在的用户授权，就是同步创建用户并授权。
此用户是从库访问主数据的用户。
ip地址不能为：%，因为主从备份中，当前创建的用户，是给从库slave访问主库master使用的，用户必须指定ip，而不能用通用ip。

grant all privileges on *.* to 'slave'@'192.168.1.71' identified by 'slave' with grant option;

flush privileges;

查看用户：

use mysql;
select user,host from user;

查看Master信息：

show master status;

1.2 Slave【主从库】配置（默认mysql数据库已安装完毕，环境：centos7）

1.2.1 修改Slave配置文件

默认位置：/etc/mysql.cnf
备份原配置文件mysql.cnf.bak，新建新的配置文件：mysql.cnf

[client]
default-character-set=utf8mb4 #客户端默认字符集

[mysqld] #服务器
port = 3306 #端口

user = mysql #提供简单用户数据
bind-address = 0.0.0.0 #当前用户可访问的主机IP, localhost. mysql用户只能在本地访问.
server-id = 2 #服务器的唯一标识

init-connect = 'SET NAMES utf8mb4' #初始化数据库链接时提供的配置信息
character-set-server = utf8mb4 #服务器基本字符集
datadir=/usr/local/mysql5.7/data

skip-name-resolve
skip-external-locking
#skip-networking
back_log = 300

max_connections = 1000 #最大客户端连接数
max_connect_errors = 6000 #单次连接最大可能出现的错误数.
open_files_limit = 65535
table_open_cache = 128 #表的开启基础缓存. 单位是个数
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M

read_buffer_size = 2M #读缓存大小
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_type = 1
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
#log_bin = master_log #日志文件名称.文件名的主体内容.mysql会自动提供后缀和文件类型.
binlog_format = mixed
expire_logs_days = 10
slow_query_log = 1
long_query_time = 1
performance_schema = 0
explicit_defaults_for_timestamp

lower_case_table_names = 1
default_storage_engine = InnoDB #默认数据库引擎
#default-storage-engine = MyISAM
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

interactive_timeout = 28800
wait_timeout = 28800

[mysqldump] #安全提供.
quick
max_allowed_packet = 16M

[myisamchk] #数据库额外辅助功能配置, 如缓存,索引缓存等.
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

• server_id配置为：2；
• log_bin可以使用默认，也可以注释不用；
• 由于从库是由主库拷贝而来，所以mysql的uuid是重复的，需要修改从库的uuid，默认路径为：/var/lib/mysql/auto.cnf 或者 /usr/local/mysql5.7/data/auto.cnf;

1.2.2 重启mysql服务

service mysqld restart

1.2.3 配置Slave

1.2.3.1 访问slave数据库

mysql -username -ppassword

1.2.3.2 停止Slave功能

stop slave;

1.2.3.3 从库配置中修改主库信息

change master to master_host='192.168.1.81',master_user='slave',master_password='bt5',master_log_file='master_log.000008';

1.2.3.4 启动Slave功能

start slave;

1.2.3.5 查看Slave配置

show slave status \G;

1.3 测试主从数据库功能

master：

create database test charset utf8;
show databases;

use test;
create table t_test(id int primary key, name varchar(32));
show tables;
desc t_test;

slave:

show databases;

use test;
show tables;
desc t_test;