mysql备份quit,mysql基础必备操作和备份恢复

最新推荐文章于 2022-08-02 10:08:47 发布

weixin_39969143

最新推荐文章于 2022-08-02 10:08:47 发布

阅读量162

点赞数

文章标签： mysql备份quit

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

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;

+-----------------------+-----------------------------------+------+-----+---------+-------+

+-----------------------+-----------------------------------+------+-----+---------+-------+

| Host | char(60) | NO | PRI | | |

| User | char(16) | NO | PRI | | |

+-----------------------+-----------------------------------+------+-----+---------+-------+

mysql> desc mysql.user;

+-----------------------+-----------------------------------+------+-----+---------+-------+

+-----------------------+-----------------------------------+------+-----+---------+-------+

| Host | char(60) | NO | PRI | | |

| User | char(16) | NO | PRI | | |

退出数据库：

命令格式：quit或exitmysql> exit

Bye

[root@localhost ~]#

mysql> quit

Bye

[root@localhost ~]#

查看表的所有记录：

语句：select * from 库.表；mysql> select *from mysql.user;

+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+

##这样看排版太乱，我们可以加上\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;

+-----------+----------+------+-----+---------+-------+

+-----------+----------+------+-----+---------+-------+

+-----------+----------+------+-----+---------+-------+

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;

+-----------+----------+------+-----+---------+-------+

+-----------+----------+------+-----+---------+-------+

+-----------+----------+------+-----+---------+-------+

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

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

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; ##查看表头

+-----------------------+-----------------------------------+------+-----+---------+-------+

+-----------------------+-----------------------------------+------+-----+---------+-------+

| Host | char(60) | NO | PRI | | |

| User | char(16) | NO | PRI | | |

| 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 | |

| 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;

+------------+-------+----------+----------+

+------------+-------+----------+----------+

+------------+-------+----------+----------+

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

例：

新建一个库：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

进入数据库验证：[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 <

create database hehe;

END

:wq

chmod +x /root/test.sh

/root/test.sh

可以不进入数据库的交互写入sql语句：[root@localhost ~]# mysql -uroot -p123123 <

> 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 <

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

weixin_39969143

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql备份quit,mysql基础必备操作和备份恢复

mysql必备操作：mysql的基础必备操作mysql的备份与恢复登录数据库：语句：mysql -u user -p password[root@localhost~]#mysql-uroot-pEnterpassword:WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidi...
复制链接

扫一扫