更改root密码
更改环境变量
修改/etc/profile文件，文件尾添加mysql的绝对路径，修改环境变量
[root@lnmp ~]# vim /etc/profileexport PATH=$PATH:/usr/local/mysql/bin/ //添加
创建MySQL密码
使用命令mysqladmin -uroot password ‘123456’为root用户创建初始密码
[root@lnmp ~]# mysqladmin -uroot password '123456'
#可以忽略warning内容，指的是明码输入屏幕不安全。
使用命令mysql -uroot -p123456，完成初始密码登录
[root@lnmp ~]# mysqladmin -uroot password '123456'
Warning: Using a password on the command line interface can be insecure.
[root@lnmp ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.47 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit
Bye
密码重置
修改配置文件/etc/my.cnf，在mysqld配置段，增加字段skip-grant，如图所示：
修改完成后，重启MySQL服务：/etc/init.d/mysqld restart
[root@lnmp ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
使用命令登入MySQL（修改的配置段，是完成忽略授权的操作，可以直接登入，无需输入用户名密码），切换到MySQL库，对user表进行更新操作
[root@lnmp ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.47 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
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 password=password('123456') where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 4 Changed: 3 Warnings: 0
修改完成后，确认新密码登录有效。把/etc/my.cnf改回原有状态，并重启MySQL服务。
[root@lnmp ~]# mysql -uroot -p123456
[root@lnmp ~]# vim /etc/my.cnf
[root@lnmp ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
连接MySQL
[root@lnmp ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.47 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
[root@lnmp ~]# mysql -uroot -p123456 -h127.0.0.1 -P3306
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.47 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
[root@lnmp ~]# mysql -uroot -p123456 -S/tmp/mysql.sock
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.47 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
[root@lnmp ~]# mysql -uroot -p123456 -e "show databases"
Warning: Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
[root@lnmp ~]#
MySQL常用命令
查询库show databases;
切换库use mysql;
查看库里的表 show tables;
查看表里字段desc db;
查看建表语句show create table db\G;
查看当前用户select user();
查看当前使用的数据库select database();
创建库 create database zz;
创建表use zz; create table t1(id int(4), name char(40));
查看当前数据库版本select version();
查看数据库状态show status;
查看各参数show variables; show variables like ‘max_connect%’;
修改参数set global max_connect_errors=1000;
查看队列show processlist; show full processlist;
MySQL创建用户以及授权
#进行授权
mysql> grant all on *.* to 'user1' identified by 'passwd';
Query OK, 0 rows affected (0.02 sec)
mysql> grant SELECT,UPDATE,INSERT on zz.* to 'user2'@'192.168.133.1' identified by 'passwd';
Query OK, 0 rows affected (0.02 sec)
mysql> grant all on zz.* to 'user3'@'%' identified by 'passwd';
Query OK, 0 rows affected (0.05 sec)
#查看授权表
mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*032197AE5731D4664921A6CCAC7CFCE6A0698693' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for user2@192.168.133.1;
+------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.133.1 |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.133.1' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' |
| GRANT SELECT, INSERT, UPDATE ON `zz`.* TO 'user2'@'192.168.133.1' |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
MySQL常用SQL语句
查看表内行数select count() from mysql.user;
查看db表内的内容select from mysql.db;
查看db表内含有db字段的内容 select db from mysql.db;
搜索查看多个字段 select db,user from mysql.db;
查询host为127.0的内容 select * from mysql.db where host like ‘192.168.%’;
向zz.t1中插入内容 insert into zz.t1 values (1, ‘abc’);
把id=1的字段内容更新成aaa update zz.t1 set name=’aaa’ where id=1;
清空db1.t1表内的内容 truncate table zz.t1;
删除db1.t1表内的内容 drop table db1.t1;
清空db1.t1数据库 drop database db1;
MySQL数据库的备份与恢复
导出整个数据库
mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump -u linuxde -p smgp_apps_linuxde > linuxde.sql
导出一个表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mysqldump -u linuxde -p smgp_apps_linuxde users > linuxde_users.sql
导出一个数据库结构
mysqldump -u linuxde -p -d —add-drop-table smgp_apps_linuxde > linuxde_db.sql
#备份库
[root@lnmp ~]# mysqldump -uroot -p000000 mysql > /tmp/mysql.sql
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
#恢复库
[root@lnmp ~]# mysql -uroot -p000000 mysql < /tmp/mysql.sql
#备份表
[root@lnmp ~]# mysqldump -uroot -p000000 mysql user > /tmp/user.sql
#恢复表
[root@lnmp ~]# mysql -uroot -p000000 mysql < /tmp/user.sql
#备份所有库
[root@lnmp ~]# mysqldump -uroot -p -A >/tmp/123.sql
Enter password:
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
#只备份表结构
[root@lnmp ~]# mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql
#因为mysqldump默认是不备份事件表的，所以提示3、13。
若有收获，就点个赞吧
0 人点赞
