主从搭建sql

1. 配置主库(my.cnf)

   server-id              = 1
   log_bin                        = /var/log/mysql/mysql-bin.log
   max_binlog_size   = 100M
   sync_binlog = 1
   binlog_format = statement
   

2. 配置从库(my.cnf)

   server-id = 2
   
   log-bin=mysql-bin
   
   replicate-do-db = test
   
   replicate-ignore-db = mysql,information_schema,performance_schema
   

3. 主库创建数据库/用户

   CREATE DATABASE test DEFAULT CHARACTER SET utf8mb4;
   use test;
   create user 'test'@'%' IDENTIFIED BY '123456';
   grant replication slave on *.* to 'test'@'%'; 
   
   

4. 从库创建数据库

   CREATE DATABASE test DEFAULT CHARACTER SET utf8mb4;
   use test;
   

5. 从库配置主从模式

   stop slave;
   
   #下边binlog名称及位置取自主库 show master status 结果
   change master to master_host='host_ip',master_user='test',master_password='123456', 'mysql-bin.000002',master_log_pos=2011;
   
   start slave;
   
   ##查看主从状态
   show slave status\G;
   
   ##重新设置主从复制位置，出错时使用
   CHANGE MASTER TO master_log_file='mysql-bin.000002',master_log_pos=2011;
   
   
   

6. 主库建表

   CREATE TABLE `t1` (
     `id` int NOT NULL AUTO_INCREMENT,
     `name` varchar(36) NOT NULL,
     `address` varchar(36) NOT NULL DEFAULT '',
     `sex` varchar(12) NOT NULL DEFAULT 'Man',
     `other` varchar(256) NOT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
   

7. 相关命令及测试语句

   1. 创建mysql容器

   rm -rf  /data/datadir/* /var/lib/mysql-files
   docker run  --name mysql -v /data/datadir:/var/lib/mysql -v /etc/mysql/my.cnf:/etc/mysql/my.cnf -v /var/lib/mysql-files:/var/lib/mysql-files -e MYSQL_ROOT_PASSWORD=123456  -p 3306:3306  -d mysql:8.0
   #上一步会自动创建名为/etc/mysql/my.cnf 的文件夹，需要把它改成文件并配置
   rm -rf /etc/mysql/my.cnf 
   vi /etc/mysql/my.cnf
   

   2. 其他命令

   insert into t1 values(2,'test1','济南','男','sss');
   show variables like 'character%';
   alter database test default character set ='utf8mb4';
   SET character_set_connection ='utf8mb4';
   SET character_set_results ='utf8mb4';
   SET character_set_client ='utf8mb4';