mysql 主从设置

11.mysql主从配置

1.修改主从数据库配置文件my.cnf

#修改主数据库的my.cnf文件
cat my.cnf

[client]
port= 3306
socket=/data/mysql/data/mysql.sock

[mysqld]
#master一般设置为1
server-id=1
#开启binlog
log-bin=mysql-bin
#binlog格式：混合。如不设置，默认为SBR模式，在导入含innodb表的数据库时会出错
binlog_format=row #(此处要启用行模式)
binlog-row-image=full #5.6以后才开始支持（此处要用full,不要用minimal）
#设置自动清理binlog日志，可能根据网站访问量和硬盘可用空间来定义这个值，15天清理一次
expire_logs_days=15
#开启gtid
gtid_mode=on
#开启从库写入binlog
log-slave-updates=on
#强制gtid一致性，开启后对于特定create table不被支持
enforce-gtid-consistency=true

port=3306
datadir=/data/mysql/data
basedir=/data/mysql
socket=/data/mysql/data/mysql.sock
user=mysql

#指定日志和pid位置#
log-error=/data/mysql/data/mysql-error.log
pid-file=/data/mysql/data/mysqld.pid

#MySQL全文索引查询关键词最小长度限制调为1
ft_min_word_len=1

#开启timestamp自动更新#
explicit_defaults_for_timestamp=true

#关闭符号链接支持#
symbolic-links=0

#不区分大小写#
lower_case_table_names=1

#禁用DNS解析#
skip-name-resolve

#INNODB重点优化参数：缓存用户表及索引数据，一般设置为系统内存的50%，但是得根据实际预留#
innodb_buffer_pool_size=1G

#MyISAM重点优化参数：指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度#
key_buffer_size=32M

#分配简单索引扫描，范围索引扫描，无索引全表扫描的表连接 缓存的大小#
join-buffer-size=2M

#在每个connection（session）第一次需要使用这个buffer的时候，一次性分配设置的内存#
sort-buffer-size=4M

#读查询操作所能使用的缓冲区大小，该参数对应的分配内存也是每连接独享#
read_buffer_size=4M

#针对按某种特定顺序(如ORDER BY子句)输出的查询结果(默认256K)加速排序操作后的读数据，提高读分类行的速度#
read_rnd_buffer_size=16M

#MyISAM表发生变化时重新排序所需的缓冲#
myisam_sort_buffer_size=128M

#关闭query-cache#
query-cache-size=0
query_cache_type=0

#临时HEAP数据表的最大长度#
tmp_table_size=256M

#重新利用保存在缓存中线程的数量#
thread_cache_size=64

#开启慢日志查询#
log-slow-admin-statements
slow-query-log=on
slow-launch-time=2
slow-query-log-file=/data/mysql/data/mysql-slow.log

#表描述符缓存大小,可减少文件打开/关闭次数#
table-open-cache=1024

#设置最大连接(用户)数#
max-connections=3000

#如果某一客户端尝试连接此MySQL服务器，但是失败（如密码错误等等）5000次，则MySQL会无条件强制阻止此客户端连接#
max_connect_errors=5000

#不写入硬盘而是写入系统缓存#
#日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新，只会在整个操作系统挂了时才可能丢数据#
innodb_flush_log_at_trx_commit=2

#限制server接受的数据包大小#
max_allowed_packet=300M

#设置编码格式#
collation-server=utf8mb4_general_ci
init-connect='SET NAMES utf8mb4'
character-set-server=utf8mb4

#忽略客户端字符集#
skip-character-set-client-handshake

#开启了log-bin创建function需要开启
log_bin_trust_function_creators=1
sql-mode="NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
group_concat_max_len=2000

#捕捉死锁到error日志
innodb_print_all_deadlocks=1
skip-grant-tables

重启主数据库
​

#修改从数据库的my.cnf文件，内容可以一样，主要是server-id=2要改变
cat my.cnf

[client]
port= 3306
socket=/data/mysql/data/mysql.sock

[mysqld]
#slave一般设置为2
server-id=2

重启从数据库
问题：
在修改MySQL的配置文件后发现mysql无法登陆，这是由于mysql密码错误，在mysql配置文件my.cnf中添加skip-grant-tables以跳过密码登陆mysql，重启mysql，登陆mysql后，修改root密码，先执行flush privileges；刷新，在执行set password for root@localhost=password('666');修改密码，在刷新flush privileges；然后退出mysql，取消配置文件中增加的skip-grant-tables重启mysql，就可以正常登陆了。

2.登陆主数据库创建主从用户并授权

grant replication slave on *.* to 'repluser'@'%' identified by 'Mysql@123ASD' with grant option;      #创建用户并分配REPLICATION SLAVE权限
#WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。可以不指定。

#grant replication slave on *.* to '用户名'@'登陆主机可用从库ip或者%所有' identified by '密码';

SELECT user,host FROM mysql.user;  #查看用户信息

DROP USER 'repluser'@'%';  #删除用户

flush privileges;  #刷新

3.查看master状态，记录二进制文件名(mysql-bin.000003)和位置(1092)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000006 |     1092 |              |                  | 0bc87904-f4e8-11eb-8628-00163e04611c:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

4.登陆从服务器修改

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='182.92.172.80',       #主数据库ip
    ->     MASTER_USER='repluser',            #创建的主从复制用户名
    ->     MASTER_PASSWORD='repluser',        #创建的主从复制用户密码
    ->     MASTER_LOG_FILE='mysql-bin.000003',#主数据库使用SHOW MASTER STATUS;查出来的文件名
    ->     MASTER_LOG_POS=73;                 #主数据库使用SHOW MASTER STATUS;查出来的文件位置
    
CHANGE MASTER TO MASTER_HOST='30.106.138.13',MASTER_USER='repluser',MASTER_PASSWORD='repluser',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=1092;

start slave;  #启动slave同步进程

show slave status\G;  #查看slave同步状态

#当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。

5.测试

#登陆主数据库，创建一个test数据库和test表
create database test;       #创建测试数据库
use test;      #进入测试库
create table test (id int not null primary key,name char(20)); #创建测试表

#登陆从数据库，查看是否有test数据库和表

数据库主备设置完成。