主从搭建sql
-
配置主库(my.cnf)
server-id = 1 log_bin = /var/log/mysql/mysql-bin.log max_binlog_size = 100M sync_binlog = 1 binlog_format = statement
-
配置从库(my.cnf)
server-id = 2 log-bin=mysql-bin replicate-do-db = test replicate-ignore-db = mysql,information_schema,performance_schema
-
主库创建数据库/用户
CREATE DATABASE test DEFAULT CHARACTER SET utf8mb4; use test; create user 'test'@'%' IDENTIFIED BY '123456'; grant replication slave on *.* to 'test'@'%'; -
从库创建数据库
CREATE DATABASE test DEFAULT CHARACTER SET utf8mb4; use test; -
从库配置主从模式
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; -
主库建表
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;
-
相关命令及测试语句
- 创建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- 其他命令
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';
