MySQL安装配置 - yiyixiaozhi/readingNotes GitHub Wiki

MySQL安装配置

docker安装mysql8

下载

docker pull mysql:8.0.21

创建mysql相关数据和日志文件夹：

cd /mybtrfs/
mkdir mysql8
cd mysql8 && mkdir data && mkdir files && mkdir log && mkdir config

启动

docker run \
    -p 3306:3306 \
    -e MYSQL_ROOT_PASSWORD=yyxz \
    -v /mybtrfs/mysql8/data:/var/lib/mysql:rw \
    -v /mybtrfs/mysql8/files:/var/lib/mysql-files:rw \
    -v /mybtrfs/mysql8/log:/var/log/mysql:rw \
    -v /mybtrfs/mysql8/config:/etc/mysql:rw \
    -v /etc/localtime:/etc/localtime:ro \
    --name mysql8 \
    --restart=always \
    -d mysql:8.0.21

mysql8/data 是数据库文件存放的地方。必须要挂载到容器外，否则容器重启一切数据消失。

mysql8/log 是数据库主生的log。建议挂载到容器外。

mysql8/config 是存放数据库的配置文件。

/etc/localtime:/etc/localtime:ro 是让容器的时钟与宿主机时钟同步，避免时区的问题，ro是read only的意思，就是只读。

如果启动失败，可以查看最近30分钟日志，方法如下：

docker logs --since 30m mysql8

防火墙放行端口

firewall-cmd  --zone=public  --add-port=3306/tcp  --permanent
firewall-cmd  --zone=public  --add-port=3307/tcp  --permanent
firewall-cmd  --reload
// 查看放行的端口
firewall-cmd  --zone=public  --list-ports

登录并创建用户

如果启动没有设置密码，也可以通过配置让首次无密码登录MySQL

[root@xa-yyxz config]# vim /mybtrfs/mysql8/config/my.cnf
[mysqld]
skip-grant-tables

用户及权限配置

寻找初始化密码

[root@iz2zehxps1f8adfzc3305pz download]# grep 'temporary password' /var/log/mysqld.log 
2019-11-11T12:10:26.778076Z 1 [Note] A temporary password is generated for root@localhost: kL4oy!D4yH,?

建立普通用户

# 寻找初始化密码
[root@iz2zehxps1f8adfzc3305pz download]# grep 'temporary password' /var/log/mysqld.log 
2019-11-11T12:10:26.778076Z 1 [Note] A temporary password is generated for root@localhost: kL4oy!D4yH,?
# 登录
mysql -h127.0.0.1 -P3306 -uroot -p
# 重置root账号密码
alter user 'root'@'localhost' IDENTIFIED BY 'yyxz';
# 创建用户
## 说明：@后面的ip地址为允许连接的客户端的ip地址，如果改为 '%'，就表示客户端没有ip地址的限制。
create user 'yyxz'@'%' identified by 'yyxz';

# 给新用户授权：
## 赋予新用户，从本地操作所有数据库.所有数据表的所有权限
grant all privileges on *.* to 'yyxz'@'127.0.0.1' identified by 'yyxz';
## 赋予新用户，从外部操作所有数据库.所有数据表的所有权限（没有外部客户端的IP限制，但本地有限制）
grant all privileges on *.* to 'yyxz'@'%' identified by 'yyxz';
## 注意：Mysql8授权写法如下：
grant all privileges on *.* to 'yyxz'@'%';
## 只允许访问单个数据库
GRANT SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE, DROP, ALTER, INDEX, CREATE VIEW, SHOW VIEW ON `yyxz_slave`.* TO 'yyxz'@'%';

# 刷新权限
FLUSH PRIVILEGES;

# 如果客户端还是无法访问，则修改密码格式。
alter user 'yyxz'@'%' identified with mysql_native_password by 'yyxz';

# 删除用户：
DROP USER user01@%;
DROP USER user01@127.0.0.1;

给root用户授权所有ip都可以访问

mysql> grant all privileges on *.* to 'root'@'%';
mysql> FLUSH PRIVILEGES;

docker下用户和权限配置示例：

# docker restart mysql8
# docker exec -it mysql8 bash
# mysql -uroot -p
Enter password: // 直接回车即可
mysql> use mysql;
mysql> select user,authentication_string from user;
mysql> update user set authentication_string='' where user='root';
# docker restart mysql8
mysql> use mysql;
mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'yyxz'; // 此处限制了root只能在本机登录
mysql> flush privileges;
mysql> create user 'yyxz'@'%' identified by 'yyxz';
mysql> alter user 'yyxz'@'%' identified with mysql_native_password by 'yyxz';
mysql> flush privileges;

删除用户和修改密码示例

删除用户
​```
mysql> DROP USER weifan;
​```
MySQL8修改用户密码
​```
mysql> CREATE USER 'yyxz'@'%' IDENTIFIED WITH mysql_native_password BY 'yyxz';
mysql> alter user 'yyxz'@'%' identified with mysql_native_password by 'yyxz';
​```

给用户配置数据库相关权限（可选）

mysql> grant all privileges on `ds%`.* to 'yyxz'@'%';
做有限的权限设定
mysql> GRANT SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE, DROP, ALTER, INDEX, CREATE VIEW, SHOW VIEW ON `ds%`.* TO 'yyxz'@'%';
mysql> flush privileges;

数据快速导入

csv格式数据后快速导入

load data local infile 'D:\\gitRepo\\private\\gitee\\yyxz\\DBDesign\\summaryl.csv' into table `yyxz_test`.`t_bill_summary` fields escaped by '\\' terminated by '\t' lines terminated by '\n' (`id`, `school_id`, `bill_count`, `should_settle_amount`, `refund_amount`, `voucher_refund_amount`, `service_charge_amount`, `order_amount`, `apply_refund_amount`, `source`, `product_tag_amount_detail`, `check_bill_status`, `check_bill_date`, `remark`, `wx_mch_id`, `create_time`, `update_time`)

如果提示错误，则打开下面的开关：

show global variables like 'local_infile';
set global local_infile=1;

主从配置

主机ip：152 从机ip：225

安装从机

cd /mybtrfs/docker/; mkdir mysql8a; cd mysql8a/; mkdir data; mkdir files; mkdir log; mkdir config;
docker run \
    -p 3307:3306 \
    -e MYSQL_ROOT_PASSWORD=yyxz \
    -v /mybtrfs/mysql8a/data:/var/lib/mysql:rw \
    -v /mybtrfs/mysql8a/files:/var/lib/mysql-files:rw \
    -v /mybtrfs/mysql8a/log:/var/log/mysql:rw \
    -v /mybtrfs/mysql8a/config:/etc/mysql:rw \
    -v /etc/localtime:/etc/localtime:ro \
    --name mysql8a \
    --restart=always \
    -d mysql:8.0.21

登录主机，建立server_id，然后建立授权账号

mysql> show variable like "server_id"; 
mysql> set global server_id 15200; 
mysql> CREATE USER 'slave_yyxz'@'%' IDENTIFIED WITH mysql_native_password BY 'yyxz'; 
mysql> GRANT replication slave ON *.* TO 'slave_yyxz'@'%'; 
mysql> flush privileges;

备份主机所有数据库：

// 主机上备份数据库
# mysqldump -uroot -pyyxz --all-databases  --lock-tables=false  > /home/yyxz/download/152db-all.sql

// 拷贝数据库到从机225上
# scp -P 22 /home/yyxz/download/152db-all.sql [email protected]:/home/yyxz/download/152db-all.sql

查看主机152位置：

mysql> show master status; 
+------------------+----------+----------------------------------+------------------+-------------------+ 
| File             | Position | Binlog_Do_DB                     | Binlog_Ignore_DB | Executed_Gtid_Set | 
+------------------+----------+----------------------------------+------------------+-------------------+ 
| mysql-bin.000001 |    52905 | yyxz_dev,yyxz_test |                  |                   | 
+------------------+----------+----------------------------------+------------------+-------------------+

恢复数据到225从机上：

# docker cp /home/yyxz/download/152db-all.sql mysql8a:/home/mysql_dump/
# docker exec -it mysql8a bash
# mysql -uroot -P3306 -pyyxz < /home/mysql_dump/152db-all.sql

// 重启从机的mysql8
docker restart mysql8a

注意：如上指令因为是全量替换，数据恢复到225从机上之后，账号也会被一并替换掉，比如root账号的密码就会被改变。

配置从机的binlog日志读取位置：

mysql> change master to
    -> master_host="192.168.1.152",
    -> master_user="slave_yyxz",
    -> master_password="yyxz",
    -> master_port=3306,
    -> master_log_file="mysql-bin.000001",
    -> master_log_pos=52905;

解锁152主机数据库：

mysql> UNLOCK TABLES;

启动225从机服务

mysql> stop slave;
mysql> start slave;
mysql> show slave status\G;

其他

如果是仅仅针对部分数据库做读写分离，配置示例如下：

文件路径：/etc/my.cnf
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
log-bin=mysql-bin
server-id=1
binlog-do-db=yyxz_dev
binlog-do-db=yyxz_test

如果出现如下错误，有两种解决方式：

1、通过重置master的binlog，然后重置slave来解决
登录master主机数据库
mysql> reset master;

登录slave从机数据库
mysql> show slave status\G;
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size'
修复方法：
mysql> stop slave;
// RESET SLAVE ALL清除从库的同步复制信息、包括连接信息和二进制文件名、位置。
mysql> reset slave;
mysql> start slave;

2、跳过个别错误：
mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;

异常处理：

> REMOTE HOST IDENTIFICATION HAS CHANGED! 解决办法
需要删除本机 ~/.ssh/known_hosts文件的192.168.5.1[需要远程的主机IP] 公钥信息

docker安装配置

参考

  • Docker安装MySQL8

https://www.cnblogs.com/badtree/articles/10130695.html

  • 使用Docker搭建MySQL服务

https://www.cnblogs.com/sablier/p/11605606.html

⚠️ **GitHub.com Fallback** ⚠️