MySQL - 日常运维操作

MySQL客户端管理工具

mycli – MySQL客户端管理工具
安装：
1、更新pip工具包:pip3 install --upgrade pip
2、使用pip3安装mycli:pip3 install mycli
使用:

[root@centos7 ~]# mycli -u<用户帐号>
Connecting to socket /var/lib/mysql/mysql.sock, owned by user mysql
Password: [输入帐号密码]

修改帐号密码

# 方法一：
MySQL root@(none):(none)> set password = password('yourpassowrd');
# 方法二：
MySQL root@(none):(none)> alter user 'username'@'localhost' identified by 'yourpassword';

授权用户远程访问权限

 MySQL root@(none):(none)> grant all privileges on *.* to 'username'@'%' identified by 'yourpassword';
 MySQL root@(none):(none)> flush privileges;

忘记root密码-重置

>>systemctl stop mysqld;

>>mysqld_safe --skip-grant-tables &  

>>mysqld -u root -p    #此时回车，直接进入mysql控制台

mysql>use mysql;

mysql>update mysql.user set authentication_string=password("youpassword") where user='root';

mysql>flush privileges;

mysql>quit;

开启MySQL操作日志

# 开启|关闭全局日志功能
:db01> set global general_log='ON'; [ON | OFF]
 
 # 查看日志服务当前状态
db01> show variables like 'general_log%';
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log      | ON                              |
| general_log_file | /var/lib/mysql/3893e696a14d.log |
+------------------+---------------------------------+

数据的备份与恢复

# 备份数据库：
[root@3893e696a14d mysqlbak]# mysqldump -h localhost -u[username] -p [databasename] > ./bakdatabasename.sql

# 数据恢复
#方法一：登录mysql使用source命令导入备份文件
@(none):db01> source /mysqlbak/bakdatabasename.sql;
#方法二
[root@3893e696a14d mysqlbak]# mysql -uxuzheng -p [databasename] < ./[bakfilename].sql 
Enter password: [yourpassword]