mysql
mysql5.7
使用docker安装mysql5.7
docker-compose.yml
version: '3'
services:
mysql:
image: mysql:5.7
restart: always
container_name: mysql57
environment:
MYSQL_USER: roma
MYSQL_DATABASE: roma
MYSQL_PASSWORD: roma
MYSQL_ROOT_PASSWORD: root
volumes:
- ./mysql:/var/lib/mysql
- ./conf/docker.cnf:/etc/mysql/conf.d/docker.cnf
ports:
- 3306:3306
conf/docker.cnf
[mysqld]
skip-host-cache
skip-name-resolve
max_connections=2000
max_user_connections=100
character_set_server = utf8mb4
- 确保主机安装好docker和docker-compose命令
- 在合适的路径下创建docker-compose.yml文件，讲上面的内容保存进去
- 在docker-compose.yml目录下创建conf文件夹，在conf文件夹下创建docker.cnf文件，讲配置内容粘贴进去
- 在docker-compose.yml目录下执行docker-compose up -d命令，启动mysql
mysql8.0
# 创建数据库
create database mooc character set utf8mb4;
# 创建用户
create user mooc;
# 修改用户密码
ALTER USER 'mooc'@'%' IDENTIFIED WITH mysql_native_password BY 'mooc';
# 授权数据库
grant all privileges on mooc.* to mooc@'%';
搭建mysql主从数据库
主数据库配置文件增加
# [必须]服务器唯一ID，默认是1，一般取IP最后一段
server-id=1
# [必须]启用二进制日志
log-bin=mysql-bin
# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db=mysql
# 设置需要同步的数据库 binlog_do_db = 数据库名；
# 如果是多个同步库，就以此格式另写几行即可。
# 如果不指明对某个具体库同步，表示同步所有库。除了binlog-ignore-db设置的忽略的库
# binlog_do_db = test #需要同步test数据库。
# 确保binlog日志写入后与硬盘同步
sync_binlog = 1
# 跳过所有的错误，继续执行复制操作
slave-skip-errors = all
从数据库配置文件增加
# [必须]服务器唯一ID，默认是1，一般取IP最后一段
server-id=2
# [必须]启用二进制日志
log-bin=mysql-bin
log-bin-index=mysql-bin.index
# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db=mysql
# 设置需要同步的数据库 binlog_do_db = 数据库名；
# 如果是多个同步库，就以此格式另写几行即可。
# 如果不指明对某个具体库同步，表示同步所有库。除了binlog-ignore-db设置的忽略的库
# binlog_do_db = test #需要同步test数据库。
# 跳过所有的错误，继续执行复制操作
slave-skip-errors = all
主数据创建从数据库账号
show variables like '%server_id%';
show master status;
# 创建数据库账号
create user slave;
# 修改用户密码
ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'slavePassword';
# 授权同步权限
grant replication slave,replication client on *.* to slave@'%';
启动从库同步
SHOW VARIABLES LIKE '%server_id%';
CHANGE MASTER TO master_host = '192.168.3.666',
master_user = 'slave',
master_password = 'slaveAa@',
master_port = 3306,
master_log_file = 'mysql-bin.000001',
master_log_pos = 1106,
master_connect_retry = 30;
start slave;
show slave status;
SHOW VARIABLES LIKE '%read_only%'; #查看只读状态
SET GLOBAL super_read_only=1; #super权限的用户只读状态 1.只读 0：可写
SET GLOBAL read_only=1; #普通权限用户读状态 1.只读 0：可写
数据库备份与恢复
# 备份数据库
mysql -uroot -p123456Aa -h192.168.3.218 \
-e 'show databases;' |
grep -E -v "Database|information_schema|mysql|performance_schema" |
xargs mysqldump -uroot -p123456 -h192.168.3.218 --databases >all.sql
# 数据库恢复
mysql -uroot -p123456Aa -h192.168.3.219 -P3307 < all.sql
检查主从一致性
- 使用docker容器来检查
docker run --rm -it perconalab/percona-toolkit:3.5.7 /bin/bash 数据库授权
CREATE TABLE `checksums` ( `db` char(64) NOT NULL, `tbl` char(64) NOT NULL, `chunk` int(11) NOT NULL, `chunk_time` float DEFAULT NULL, `chunk_index` varchar(200) DEFAULT NULL, `lower_boundary` text, `upper_boundary` text, `this_crc` char(40) NOT NULL, `this_cnt` int(11) NOT NULL, `master_crc` char(40) DEFAULT NULL, `master_cnt` int(11) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`db`, `tbl`, `chunk`), KEY `ts_db_tbl` (`ts`, `db`, `tbl`) ) ENGINE = InnoDB; -- slave GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'root'@'%'; flush privileges; -- master GRANT CREATE,INSERT,SELECT,DELETE,UPDATE,LOCK TABLES,PROCESS,SUPER,REPLICATION SLAVE ON *.* TO 'root'@'%'; flush privileges;
检查主从一致性
pt-table-checksum --nocheck-binlog-format --ignore-databases=traffic --create-replicate-table --nocheck-replication-filters --replicate=demo.checksums -u'root' -p'123456' -h192.168.3.219 -P3306
pt-table-sync --replicate=demo.checksums h=192.168.3.219,u=root,p=123456,P=3307 h=61.136.101.80,u=root,p=123456,P=3306 --print
修改数据库和所有表的collate
-- 修改数据库的collate
ALTER DATABASE task_monitor
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 修改所有表的字符集和排序规则
SELECT
CONCAT('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS alter_table_query
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = 'task_monitor';