112 lines
3.1 KiB
Markdown
112 lines
3.1 KiB
Markdown
# mysql主备搭建
|
|
```sql
|
|
CREATE DATABASE IF NOT EXISTS kodbox1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
|
|
create user 'sqladmin'@'%' identified by 'em123456';
|
|
grant all on *.* to 'sqladmin'@'%';
|
|
```
|
|
## 1045(28000)错误解决方法
|
|
|
|
^055b1a
|
|
|
|
```
|
|
原因:
|
|
root用户不能远程登录,
|
|
|
|
解决方法:
|
|
1.可以在容器内输入mysql直接以root用户进入数据库,修改root用户远程权限
|
|
2.在mysql配置文件末尾添加skip-grant-tables,跳过权限检查,配置文件在/etc/mysql/my.cnf
|
|
```
|
|
# MySQL安装
|
|
## centos使用yum安装mysql8.0
|
|
```sh
|
|
# centos7-mysql8.0版本
|
|
wget https://dev.mysql.com/get/mysql80-community-release-el7-8.noarch.rpm
|
|
# 安装存储库
|
|
yum install -y mysql80-community-release-el7-8.noarch.rpm
|
|
# 禁用5.7的存储库,启用8.0的存储库
|
|
yum-config-manager --disable mysql57-community
|
|
yum-config-manager --enable mysql80-community
|
|
# 安装服务端和客户端
|
|
yum install -y mysql-community-server mysql-community-client
|
|
# 启动mysql服务端
|
|
systemctl start mysqld
|
|
# 查询root密码
|
|
grep 'temporary password' /var/log/mysqld.log
|
|
```
|
|
## 创建MySQL容器
|
|
|
|
^a6aefb
|
|
|
|
```sh
|
|
# 创建容器网络
|
|
docker network create -d bridge vlan100
|
|
# mysql8.0版本
|
|
docker run -itd --name ms10 -h ms10 --restart=always --network vlan100 -e MYSQL_ROOT_PASSWORD=abc123456 -v /var/mysql/ms10/data:/var/lib/mysql -v /var/mysql/ms10/log:/var/log -p 8010:3306 mysql:latest
|
|
|
|
# mysql5.7版本
|
|
docker run -itd --name ms10 --restart=always --network vlan100 -e MYSQL_ROOT_PASSWORD=abc123456 -p 9901:3306 mysql:5.7
|
|
|
|
|
|
```
|
|
# mysql基本用法
|
|
```mysql
|
|
# 创建数据库
|
|
CREATE DATABASE IF NOT EXISTS dbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
|
|
# 查看数据库字符集
|
|
show variables like %character%;
|
|
show variables like %collation%;
|
|
# 创建用户,%代表任意主机访问权限,localhost代表本地访问权限
|
|
create user 'username'@'%' identified by 'password';
|
|
# 查询所有用户
|
|
select user,host from mysql.user;
|
|
# 查询当前用户
|
|
select current_user;
|
|
select user();
|
|
# 修改用户名
|
|
rename user 'user1'@'%' to 'user2'@'%';
|
|
# 删除用户
|
|
drop user 'user1'@'%';
|
|
delete from mysql.user where user='user1'
|
|
# 修改密码
|
|
alter user 'user1'@'%' identified by 'newpassword';
|
|
# 查看用户权限
|
|
show grants for 'user1'@'%';
|
|
# 查看所有数据库
|
|
show databases;
|
|
# 查看所有数据表;
|
|
show tables;
|
|
# 添加权限,赋予用户user1对testdb数据库的所有权限,*代表所有数据表,权限有select,delete,update,create,drop
|
|
grant all on testdb.* to 'user1'@'%';
|
|
# 添加查询权限,给user1用户添加testdb的name表的查询权限
|
|
grant select on testdb.name to 'user1'@'%';
|
|
# 撤销权限,
|
|
revoke all on testdb.* from 'user1'@'%';
|
|
# 刷新系统权限表,即时生效
|
|
flush privileges;
|
|
|
|
```
|
|
# MySQL常用操作
|
|
```sh
|
|
# 主配置文件：
|
|
/etc/my.cnf
|
|
# 数据库文件：
|
|
/var/lib/mysql
|
|
# 日志文件：
|
|
/var/log
|
|
|
|
# 获取5.7版本以上的随机密码
|
|
grep 'temporary password' /var/log/mysqld.log
|
|
|
|
# 登录
|
|
mysql -uroot -p
|
|
# 修改密码
|
|
SET PASSWORD = PASSWORD('new password')
|
|
|
|
# 跳过密码验证
|
|
echo "skip-grant-tables" >> /etc/my.cnf
|
|
# 重启msql
|
|
|
|
# 重置密码
|
|
update user set password=password("你的新密码") where user="root"
|
|
|
|
``` |