继续尝试Mysql的主备

妖狐艹你老母 2022-05-15 04:28 176阅读 0赞

## 本次尝试使用docker来搭建mysql的主备 ##

首先拉取Mysql的docker 镜像

docker pull mysql

然后编写mysql的配置文件并创建container

[mysqld]
    server-id = 1
    gtid-mode=on
    enforce-gtid-consistency=1
    port = 3306
    log-bin =  /var/log/mysql/mysql-bin.log
    log-bin_index =/var/log/mysql/mysql-bin.index
    binlog-format=row
    skip_slave_start=1
    socket = /tmp/mysql.sock
    basedir = /usr/local/mysql
    datadir = /var/lib/mysql
    pid-file = /var/run/mysqld/mysqld.pid
    init-connect = 'SET NAMES utf8mb4'
    character-set-server = utf8mb4
    log_error = /var/log/mysql/mysql-error.log
    default_storage_engine = InnoDB
    expire_logs_days = 7
    max_connections = 3000
    max_connect_errors = 6000
    slow_query_log = 1
    long_query_time = 1
    slow_query_log_file = /var/log/mysql/mysql-slow.log
    performance_schema = 0
    lower_case_table_names = 1
    innodb_flush_log_at_trx_commit = 2
    interactive_timeout = 28800
    wait_timeout = 28800
    max_allowed_packet = 512M
    connect_timeout = 60
    net_read_timeout = 120
    innodb_buffer_pool_size = 16G
    group_concat_max_len = 102400
    
    
    [client]
    port = 3306
    socket = /tmp/mysql.sock
    default-character-set = utf8mb4
    
    [mysqldump]
    quick
    max_allowed_packet = 1024M
    
    [myisamchk]
    key_buffer_size = 8M
    sort_buffer_size = 8M
    read_buffer = 4M
    write_buffer = 4M

其中要挂载出来的目录为:

conf目录 为 /etc/mysql/
    log目录为 /var/log/mysql/
    data目录为 /var/lib/mysql/

所以新挂载的mysql应该这样写:

docker run -d -v /mnt/data/mysql/docker/conf/:/etc/mysql/ \
    -v /mnt/data/mysql/docker/data/:/var/lib/mysql/ \ -v /mnt/data/mysql/docker/log/:/var/log/mysql/ \ -v /mnt/data/mysql/docker/file/:/var/lib/mysql-files/ \ -e MYSQL_ROOT_PASSWORD=my-secret-pw \ -p 3304:3306 --name my-master mysql

启动docker,然后进入msyql的命令行

msyql -h 10.20.100.235 -P 3304 -uroot -p;
    create user wangwei@'10.20.100.%' identified by 'password';
    create database crawler;
    grant all on crawler.* to wangwei@'10.20.100.%';

然后登录wangwei这个账号,进行验证,验证成功  
到这里,主机创建成功,建立主机上的复制账号

create user repl identified by 'Pass@word1';
    grant REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.%';
    flush privileges;
    
    show master status;//查看主库的状态, 其中的mysql-bin.000003是下面从库要用的 +------------------+----------+--------------+------------------+-------------------------------------------+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------------------------------+
    | mysql-bin.000003 | 3624 | | | c69e4785-a783-11e8-ba8e-0242ac110002:1-21 | +------------------+----------+--------------+------------------+-------------------------------------------+
    1 row in set (0.00 sec)

配置从库的配置文件 my.cnf

[mysqld]
    server-id = 2
    port = 3306
    gtid-mode=on
    enforce-gtid-consistency=1
    relay-log = /var/log/mysql_binlog/relay-log
    relay-log-index = /var/log/mysql_binlog/relay-log.index
    log-bin =  /var/log/mysql/mysql-bin.log
    log-bin_index =/var/log/mysql/mysql-bin.index
    binlog-format=row
    skip_slave_start=1
    log-slave-updates=true
    basedir = /usr/local/mysql
    datadir = /var/lib/mysql
    log_error = /var/log/mysql/mysql-error.log
    default_storage_engine = InnoDB
    super_read_only = 1
    read_only = 1
    expire_logs_days = 7
    max_connections = 3000
    max_connect_errors = 6000
    slow_query_log = 1
    long_query_time = 1
    slow_query_log_file = /var/log/mysql/mysql-slow.log
    
    
    [client]
    port = 3306
    socket = /tmp/mysql.sock
    default-character-set = utf8mb4
    
    [mysqldump]
    quick
    max_allowed_packet = 1024M
    
    [myisamchk]
    key_buffer_size = 8M
    sort_buffer_size = 8M
    read_buffer = 4M
    write_buffer = 4M

然后从库执行如下的命令:

docker run -d -v /mnt/data/mysql/docker/conf/:/etc/mysql/ \
    -v /mnt/data/mysql/docker/data/:/var/lib/mysql/ \ -v /mnt/data/mysql/docker/log/:/var/log/mysql/ \ -v /mnt/data/mysql/docker/files/:/var/lib/mysql-files/ \ -v /mnt/data/mysql/docker/binlog/:/var/log/mysql_binlog/ \ -v /mnt/data/mysql/docker/errmsg/:/usr/local/mysql/share/ \ -e MYSQL_ROOT_PASSWORD=Pass@word1 \ -p 3304:3306 --name my-slave mysql

将从库启动,然后在从库的命令行中,执行下面的语句

change master to master_host='10.20.100.235',
    master_port=3304,
    master_user='repl',
    master_password='Pass@word1', 
    master_log_file='mysql-bin.000003', //主库查询出来的bin
    master_log_pos=0;
    
    start slave;
    show slave status\G; //查看从库装状态

有时候启动从库的slave的时候回遇到如下的问题:

Could not initialize master info structure; more error 
    messages can be found in the MySQL error log

可以通过如下的命令来解决

reset slave;
        start slave IO_THREAD;
        stop slave IO_THREAD;
        reset slave;
        start slave;

最后就能正常的同步主库和从库了.  
测试在主库中建立数据表,和插入数据

create table tb_asset (id int,name varchar(100));
    insert into tb_asset values(1,'aaa');
    select * from tb_asset;

在从库中,可以正常的查询到数据  
在从库中插入数据

insert into tb_asset values(2,'bbb');
    出现报错
    ERROR 1290 (HY000): The MySQL server is running with 
    the --super-read-only option so it cannot execute this statement

嗯,正常保存,不能让从库单独的修改.  
到此 , 主从的mysql完成.