mysql必备操作：
mysql的基础必备操作
mysql的备份与恢复
登录数据库：
语句：mysql -u user -p password
[root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.1.71 Source distribution Copyright (c) 2000, 2013, 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;
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 2 rows in set (0.00 sec) mysql>
进入库：
语法：use 库名
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
查看表：
语法：show tabels;
mysql> show tables; ##前提是先进入库才能查看表。表在库中的。 +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | ndb_binlog_index | | plugin | | proc | | procs_priv | | servers | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 23 rows in set (0.00 sec)
查看标的结构表头信息：
语法：desc 表名；（进入库后查看） desc 库名.表名 (库外查看)
mysql> desc user; +-----------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+-----------------------------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | +-----------------------+-----------------------------------+------+-----+---------+-------+ mysql> desc mysql.user; +-----------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+-----------------------------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | |
退出数据库：
命令格式：quit或exit
mysql> exit Bye [root@localhost ~]# mysql> quit Bye [root@localhost ~]#
查看表的所有记录：
语句：select * from 库.表；
mysql> select *from mysql.user; +-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | C ##这样看排版太乱，我们可以加上\G,格式输出 mysql> select *from mysql.user \G *************************** 1. row *************************** Host: localhost User: root Password: *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y ......
筛选查看信息：
语句：select 查看的内容 from 库.表；
mysql> select host,user,password from mysql.user; +-----------+------+-------------------------------------------+ | host | user | password | +-----------+------+-------------------------------------------+ | localhost | root | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | | 127.0.0.1 | root | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | +-----------+------+-------------------------------------------+ 2 rows in set (0.00 sec)
写入数据，或更新数据：
语句格式：update 库.表 set 要更改的字段=更改为 where 匹配的字段
例：更改root的密码
mysql> update mysql.user set password=password("123321") where user="root";
##这是将登录数据库root的密码改为123123
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql>删除指定数据：
语法格式：delete from 库.表 where 匹配的字符;
##删除用户为空的数据记录
mysql> delete from mysql.user where user=""; Query OK, 0 rows affected (0.00 sec) mysql>
创建库：
语法格式：create database 新建库名；
mysql> create database auth; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | auth | | mysql | +--------------------+ 3 rows in set (0.00 sec
创建表：
语法格式：create table 库.表(字段1 char(字符串字节)， 字段2 char(最大字符窜字节))；
mysql> create table auth.users(user_name char(16) not null, pass_wd char(24) default '',primary key (user_name)); Query OK, 0 rows affected (0.01 sec) mysql> use auth; Database changed mysql> show tables; +----------------+ | Tables_in_auth | +----------------+ | users | +----------------+ 1 row in set (0.00 sec) mysql> desc users; +-----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------+------+-----+---------+-------+ | user_name | char(16) | NO | PRI | NULL | | | pass_wd | char(24) | YES | | | | +-----------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
插入记录：
语法格式： insert into 库.表 values('字段一对应的字符','字段二对应的字符')；
#就是user_name里插入的字符和pass_wd里插入的字符
mysql> insert into auth.users values('leslie','123123');
Query OK, 1 row affected (0.00 sec)
mysql> desc users;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| user_name | char(16) | NO | PRI | NULL | |
| pass_wd | char(24) | YES | | | |
+-----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from auth.users \G;
*************************** 1. row ***************************
user_name: leslie
pass_wd: 123123
1 row in set (0.00 sec)删除表：
语法格式：drop table 库.表；
mysql> drop table auth.users; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +----------------+ | Tables_in_auth | +----------------+ | user | +----------------+ 1 row in set (0.00 sec) mysql> drop table auth.user; Query OK, 0 rows affected (0.00 sec) mysql> show tables; Empty set (0.00 sec) mysql>
删除库:
语法格式：drop database 库名；
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | auth | | mysql | +--------------------+ 3 rows in set (0.00 sec) mysql> drop database auth; Query OK, 0 rows affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 2 rows in set (0.00 sec) mysql>
3.mysql的权限管理
授权格式：
grant all on 库.* to 用户@客户机地址 identified by ‘密码’;
查看授权：
show grants for 用户@客户机地址；
撤销权限：
revoke 权限列表 on 库.* from 用户@客户机地址;
mysql> grant all privileges on mysql.user to root@192.168.200.202 identified by '123123'; Query OK, 0 rows affected (0.00 sec) | ##使用被授权的主机远程连接数据库 [root@localhost ~]# mysql -uroot -p123123 -h192.168.200.203 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.1.71 Source distribution Copyright (c) 2000, 2011, 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 | +--------------------+ 2 rows in set (0.00 sec) mysql>
查看权限
mysql> show grants for 'root'@'192.168.200.202'; +-------------------------------------------------------------------------------------------------------------------+ | Grants for root@192.168.200.202 | +-------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'root'@'192.168.200.202' IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' | | GRANT ALL PRIVILEGES ON `mysql`.`user` TO 'root'@'192.168.200.202' | +-------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
撤销权限：
当给主机和用户授权时就是再mysql.user表中新建了个用户，还有权限都存在这张表里，
使用撤销权限后，用户依然存在。还是可以连接上数据库的。要想彻底删除，直接把mysql.user表中找到授权新建的用户名，删除这条记录就可以了，刷新权限后，被撤销权限的用户就无法登陆数据库了。
revoke select,delete on mysql.user from 'useradm'@'192.168.200.254';
delete from mysql.user where user='useradm';
flush privileges;
mysql> grant all privileges on mysql.user to leslie@192.168.100.100 identified by '123123'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for leslie@192.168.100.100; +---------------------------------------------------------------------------------------------------------------------+ | Grants for leslie@192.168.100.100 | +---------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'leslie'@'192.168.100.100' IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' | | GRANT ALL PRIVILEGES ON `mysql`.`user` TO 'leslie'@'192.168.100.100' | +---------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> revoke all on mysql.user from leslie@192.168.100.100; Query OK, 0 rows affected (0.00 sec) mysql> show grants for leslie@192.168.100.100; +---------------------------------------------------------------------------------------------------------------------+ | Grants for leslie@192.168.100.100 | +---------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'leslie'@'192.168.100.100' IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' | +---------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> delete from mysql.user where user='leslie'; Query OK, 1 row affected (0.00 sec) mysql> show grants for leslie@192.168.100.100; +---------------------------------------------------------------------------------------------------------------------+ | Grants for leslie@192.168.100.100 | +---------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'leslie'@'192.168.100.100' IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' | +---------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> show grants for leslie@192.168.100.100; ERROR 1141 (42000): There is no such grant defined for user 'leslie' on host '192.168.100.100'
4.备份与恢复mysql
物理备份：
复制数据文件实现备份
mysql> create database backup; ##新建空的数据库
Query OK, 1 row affected (0.00 sec)
mysql> quit ##退出数据库
Bye
[root@localhost ~]# cat /etc/my.cnf ##查看mysql的主配置文件，可以看到数据文件目录路径
[mysqld] 在/var/lib/mysql下
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@localhost ~]# /etc/init.d/mysqld stop ##停止mysql防止数据再写入
停止 mysqld： [确定]
[root@localhost ~]# cd /var/lib/mysql/ ##cd到数据库数据目录下，
可以看到每一个库都是一个文件夹，每一个表又有多个文件组成。
[root@localhost mysql]# ls
backup ibdata1 ib_logfile0 ib_logfile1 mysql
[root@localhost mysql]# ls mysql/
columns_priv.frm general_log.CSM help_topic.frm proc.frm tables_priv.frm time_zone_transition.frm
columns_priv.MYD general_log.CSV help_topic.MYD proc.MYD tables_priv.MYD time_zone_transition.MYD
columns_priv.MYI general_log.frm help_topic.MYI proc.MYI tables_priv.MYI time_zone_transition.MYI
db.frm help_category.frm host.frm procs_priv.frm time_zone.frm time_zone_transition_type.frm
db.MYD help_category.MYD host.MYD procs_priv.MYD time_zone_leap_second.frm time_zone_transition_type.MYD
db.MYI help_category.MYI host.MYI procs_priv.MYI time_zone_leap_second.MYD time_zone_transition_type.MYI
event.frm help_keyword.frm ndb_binlog_index.frm servers.frm time_zone_leap_second.MYI user.frm
event.MYD help_keyword.MYD ndb_binlog_index.MYD servers.MYD time_zone.MYD user.MYD
event.MYI help_keyword.MYI ndb_binlog_index.MYI servers.MYI time_zone.MYI user.MYI
func.frm help_relation.frm plugin.frm slow_log.CSM time_zone_name.frm
func.MYD help_relation.MYD plugin.MYD slow_log.CSV time_zone_name.MYD
func.MYI help_relation.MYI plugin.MYI slow_log.frm time_zone_name.MYI
复制mysql/user.*文件 也就是复制数据库mysql.表user的数据 复制到backup目录，
就是我刚开始新建的库生成的文件夹。把数据导入到这里。
[root@localhost mysql]# cp -rf mysql/user.* backup/
[root@localhost mysql]# ls backup/
db.opt user.frm user.MYD user.MYI
给backup这个文件夹授权
[root@localhost mysql]# chown -R mysql:mysql backup/
[root@localhost mysql]# chmod 755 backup
[root@localhost mysql]# chmod 660 backup/*
重启服务:
[root@localhost mysql]# /etc/init.d/mysqld start
正在启动 mysqld： [确定]
登陆数据库验证：
[root@localhost mysql]# mysql -uroot -p123123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.71 Source distribution
Copyright (c) 2000, 2013, 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> use backup; ##进入backup库
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> show tables; ##可以看到新建的库中有我们导入的user表
+------------------+
| Tables_in_backup |
+------------------+
| user |
+------------------+
1 row in set (0.00 sec)
mysql> desc user; ##查看表头
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
39 rows in set (0.00 sec)mysql的冷备份：
冷备份都需要先把服务停止才能进行操作：
[root@localhost mysql]# /etc/init.d/mysqld stop
停止 mysqld： [确定]
打包备份mysql的整个数据文件目录：
[root@localhost mysql]# tar jcf /opt/mysql-bak-$(date +%F).tar.xz ../mysql/
tar: 从成员名中删除开头的“../”
[root@localhost mysql]# ls /opt/
mysql-bak-2017-08-17.tar.xz
模拟故障把数据库drop删除掉：
[root@localhost mysql]# /etc/init.d/mysqld start
正在启动 mysqld： [确定]
[root@localhost mysql]# mysql -uroot -p123123
mysql> drop database backup; ##删除数据库
Query OK, 1 row affected (0.02 sec)
mysql> show databases; ##现在没有backup这个数据库了
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)
mysql> quit
Bye
进入到备份目录解压备份的数据库文件：
[root@localhost mysql]# cd /opt/
[root@localhost opt]# tar jxf mysql-bak-2017-08-17.tar.xz
[root@localhost opt]# ls
mysql mysql-bak-2017-08-17.tar.xz
[root@localhost opt]# cd mysql
[root@localhost mysql]# ls
backup ibdata1 ib_logfile0 ib_logfile1 mysql
将意外删除的库复制到mysql数据根目录下：
[root@localhost mysql]# cp -rf backup/ /var/lib/mysql/
[root@localhost mysql]# cd /var/lib/mysql/
[root@localhost mysql]# ls
backup ibdata1 ib_logfile0 ib_logfile1 mysql mysql.sock
给mysql根目录下复制过来的库授权
[root@localhost mysql]# chown -R mysql:mysql backup/
[root@localhost mysql]# chmod 755 backup
[root@localhost mysql]# chmod 660 backup/*
重启服务：
[root@localhost mysql]# /etc/init.d/mysqld restart
停止 mysqld： [确定]
正在启动 mysqld： [确定]
进入数据库验证：
[root@localhost mysql]# mysql -uroot -p123123
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| backup |
| mysql |
+--------------------+
3 rows in set (0.00 sec)
mysql> use backup;
mysql> show tables;
+------------------+
| Tables_in_backup |
+------------------+
| user |
+------------------+
1 row in set (0.00 sec)
可以查看一下表的状态是否恢复正常；
mysql> check table user;
+------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+-------+----------+----------+
| mysql.user | check | status | OK |
+------------+-------+----------+----------+
1 row in set (0.00 sec)
mysql> quit
Bye
[root@localhost mysql]#
在线备份；mysqldump
netstat -utpln |grep 3306 ##确保mysql启动
mysqldump -uroot -p123123 --all-databases >/opt/all.sql #备份
mysqldump -uroot -p123123 --all-databases --lock-talbes=0 >/opt/all.sql
mysql -uroot -p123123 </opt/all.sql ##恢复
例：
新建一个库：
mysql> create database auth; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | auth | | mysql | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> quit Bye
使用mysqldump工具实现热备：
语法： mysqldump -u用户 -p 密码 库.表 >/dump的位置
[root@localhost ~]# mysqldump -uroot -p123123 --all-databases >/opt/alldatabase.sql -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly. [root@localhost ~]# ls /opt/alldatabase.sql /opt/alldatabase.sql
模拟故障：进入数据库删除创建的库auth
[root@localhost ~]# mysql -uroot -p123123 mysql> drop database auth; Query OK, 0 rows affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 3 rows in set (0.00 sec) mysql> quit Bye
恢复备份： 将
[root@localhost ~]# mysql -uroot -p123123 </opt/alldatabase.sql
进入数据库验证：
[root@localhost ~]# mysql -uroot -p123123 mysql> show databases; +--------------------+ | Database |- +--------------------+ | information_schema | | auth | | mysql | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> quit Bye [root@localhost ~]#
在bash中操作mysql：去交互式
vi /root/test.sh
mysql -uroot -p123123 <<END
create database hehe;
END
:wq
chmod +x /root/test.sh
/root/test.sh
可以不进入数据库的交互写入sql语句：
[root@localhost ~]# mysql -uroot -p123123 <<end > create database hehe > end [root@localhost ~]# mysql -uroot -p123123 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | auth | | hehe | | mysql | | test | +--------------------+ 5 rows in set (0.00 sec) 脚本的方式实现sql语句注入： [root@localhost ~]# vi test.sql ##新建脚本 [root@localhost ~]# cat test.sql ##脚本内容 mysql -uroot -p123123 <<end create database xixi; end 给予执行权限，执行脚本。 [root@localhost ~]# sh -x test.sql + mysql -uroot -p123123 进入数据库查看验证 [root@localhost ~]# mysql -uroot -p123123 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | auth | | hehe | | mysql | | test | | xixi | +--------------------+ 6 rows in set (0.00 sec) mysql>
5.mysql忘记密码的解决方案：
vim /etc/my.cnf
[mysqld]
skip-grant-tables ##添加该行，跳过密码验证
:wq
/etc/init.d/mysqld restart
mysql ##登录后操作
update mysql.user set password=password("123123") where user="root"; ##修改root密码
exit
vim /etc/my.cnf
[mysqld]
#skip-grant-tables ##注释该行
:wq
/etc/init.d/mysqld restart
例： mysql忘记密码
修改mysql的配置文件my.cnf
[root@localhost ~]# vi /etc/my.cnf [root@localhost ~]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-grant-tables ##添加这一行，就是跳过密码的验证 user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
重启服务： 使用新密码登录
[root@localhost ~]# /etc/init.d/mysqld restart 停止 mysqld： [确定] 正在启动 mysqld： [确定]
直接输入mysql登录到mysql交互
[root@localhost ~]# mysql
更改mysql.user这个表中的root密码，这个时登录mysql的账号
mysql> update mysql.user set password=password("123123") where user="root";
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3 Changed: 2 Warnings: 0
mysql> quit
Bye
将跳过密码验证这一行的，跳过。
[root@localhost ~]# vi /etc/my.cnf [root@localhost ~]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock #skip-grant-tables user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
重启服务 使用新密码登录
[root@localhost ~]# /etc/init.d/mysqld restart 停止 mysqld： [确定] 正在启动 mysqld： [确定] [root@localhost ~]# [root@localhost ~]# mysql -uroot -p123123 -s mysql> show databases; Database information_schema auth hehe mysql test xixi
6.单独管理用户：
用户管理
mysql>use mysql;
mysql> select host,user,password from user ;
mysql>create user linuxfan identified by '123123'; ##identified by 会将纯文本密码加密作为散列值存储
mysql>rename user linuxfan to fage；##mysql 5之后可以使用，之前需要使用update 更新user表
mysql> set password for fage=password('123');
mysql> update mysql.user set password=password('123') where user='fage';
mysql> show grants for fage;查看用户权限
mysql> grant select on mysql.user to fage; ##赋予权限
mysql> revoke select on mysql.user from fage; ##如果权限不存在会报错
mysql>drop user fage; ##mysql5之前删除用户时必须先使用revoke 删除用户权限，然后删除用户，mysql5之后drop 命令可以删除用户的同时删除用户的相关权限
7.设置mysql5.5显示中文名：
vi /etc/my.cnf
[client]
default-character-set = utf8
[mysqld]
character-set-server = utf8
init_connect='SET NAMES utf8'
:wq
/etc/init.d/mysqld restart
