Mysql 运维

目录

一、mysql 常见问题

1、mysql密码忘记，重置密码（mysql 5.7）

1.1、更改my.cnf文件，在[mysqld]模块下添加skip-grant-tables

1.2、重新启动mysql

1.3、登录mysql

1.4、去掉skip-grant-tables，注释掉，更改my.cnf文件，在[mysqld]模块下添加skip-grant-tables

1.5、重新启动mysql

2、开启远程访问

2.1、开启远程访问权限的方式,主要包括两种:改表法和授权法

---

一、mysql 常见问题

1、mysql密码忘记，重置密码（mysql 5.7）

1.1、更改my.cnf文件，在[mysqld]模块下添加skip-grant-tables

[mysqld]
server-id=1306
port=3306
log-bin=mysql-bin
log-slave-updates=1
datadir=/var/lib/mysql/
max_connections=2000

wait_timeout=86400
interactive_timeout=86400

bulk_insert_buffer_size=120M
max_allowed_packet=32M
net_buffer_length=256k
sort_buffer_size=2M

innodb_buffer_pool_size=1280M
innodb_log_buffer_size=64M

transaction-isolation=READ-COMMITTED
binlog_format=ROW

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
symbolic-links=0

log_bin_trust_function_creators=true
character_set_server=utf8
collation_server=utf8_bin
lower_case_table_names=1

innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
skip-host-cache
skip-name-resolve
skip-grant-tables

1.2、重新启动mysql

# docker 重启方式
docker-compose down
docker-compose up -d

# systemctl 系统服务重启方式
systemctl restart mysqld

1.3、登录mysql

root@sddi-24:/# mysql -uroot -p -hlocalhost
Enter password: 此处直接回车

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> UPDATE user SET authentication string=password('newpassword') WHERE User='root';
mysql> flush privileges;
mysql> exit
Bye

1.4、去掉skip-grant-tables，注释掉，更改my.cnf文件，在[mysqld]模块下添加skip-grant-tables

[mysqld]
server-id=1306
port=3306
log-bin=mysql-bin
log-slave-updates=1
datadir=/var/lib/mysql/
max_connections=2000

wait_timeout=86400
interactive_timeout=86400

bulk_insert_buffer_size=120M
max_allowed_packet=32M
net_buffer_length=256k
sort_buffer_size=2M

innodb_buffer_pool_size=1280M
innodb_log_buffer_size=64M

transaction-isolation=READ-COMMITTED
binlog_format=ROW

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
symbolic-links=0

log_bin_trust_function_creators=true
character_set_server=utf8
collation_server=utf8_bin
lower_case_table_names=1

innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
skip-host-cache
skip-name-resolve
# skip-grant-tables

1.5、重新启动mysql

# docker 重启方式
docker-compose down
docker-compose up -d

# systemctl 系统服务重启方式
systemctl restart mysqld

1.6、如过提示密码需要重置

ALTER USER 'root'@'localhost' IDENTIFIED BY '<new_password>';

 

2、开启远程访问

2.1、开启远程访问权限的方式,主要包括两种:改表法和授权法

• 查看MySQL当前访问远程访问权限

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select User,authentication_string,Host from user;
+---------------+-------------------------------------------+-----------+
| User          | authentication_string                     | Host      |
+---------------+-------------------------------------------+-----------+
| root          | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | localhost |
| mysql.session | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | localhost |
| mysql.sys     | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | localhost |
| root          | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | %         |

• 方式一：改表法

顾名思义,该方法就是直接修改更改"mysql"数据库里的"user"表里的"host"项，从"localhost"改为"%"

update user set host='%' where user='root';

• 方式二：授权法

通过GRANT命令可以授予主机远程访问权限

-- 赋予任何主机访问权限：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

-- 允许指定主机(IP地址)访问权限：

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'root' WITH GRANT OPTION;

-- 通过GRANT命令赋权后,需要通过FLUSH PRIVILEGES刷新权限表使修改生效：

flush privileges;