mysql 日常维护总结_mysql维护管理的几点小技巧（自我总结）

一、mysql数据库用户密码修改方法

(1)、在知道mysql数据库root用户密码条件下修改root用户密码wxsemico

方法一：

[root@centos6 ~]# mysql -u root -p

Enter password: 输入root密码mysql> show databases;

mysql> use mysql;

mysql> update user set password=password('wxsemico') where user='root';

mysql> flush privileges;

mysql> exit

方法二：

[root@centos6 ~]# mysqladmin -u root -p2013@wxsemicopassword wxsemico

/红色原密码，蓝色新密码[root@centos6 ~]# mysql -u root -pwxsemicoWelcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 386

Server version: 5.1.42-log Source distribution

Copyright (c) 2000, 2012, 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>

/使用新密码wxsemico登录成功

(3)、在忘记mysql数据库root用户密码条件下修改root用户密码为2013@wxsemico

[root@centos6 ~]# mysqladmin -u root -p shutdown

Enter password:

140523 18:50:33 mysqld_safe mysqld from pid file /usr/local/mysql/var/centos6.pid ended

[1]+  Done                    ./mysqld_safe --user=root --skip-grant-tables  (wd: /usr/local/mysql/bin)

(wd now: ~)

[root@centos6 ~]# ps -ef | grep mysql

root      2751  1971  0 18:50 pts/0    00:00:00 grep mysql

/先关闭掉mysql数据库服务

/由于mysql为源码安装方式先切换至mysql安装目录下的bin目录

[root@centos6 ~]# cd /usr/local/mysql/bin/

[root@centos6 bin]# ll

total 16600

-rwxr-xr-x. 1 mysql mysql   10664 May 22  2013 innochecksum

-rwxr-xr-x. 1 mysql mysql    1424 May 22  2013 msql2mysql

-rwxr-xr-x. 1 mysql mysql 1794627 May 22  2013 myisamchk

-rwxr-xr-x. 1 mysql mysql 1667899 May 22  2013 myisam_ftdump

-rwxr-xr-x. 1 mysql mysql 1667299 May 22  2013 myisamlog

-rwxr-xr-x. 1 mysql mysql 1711644 May 22  2013 myisampack

-rwxr-xr-x. 1 mysql mysql 1398357 May 22  2013 my_print_defaults

-rwxr-xr-x. 1 mysql mysql  269500 May 22  2013 mysql

-rwxr-xr-x. 1 mysql mysql  110848 May 22  2013 mysqlaccess

-rwxr-xr-x. 1 mysql mysql   31997 May 22  2013 mysqladmin

-rwxr-xr-x. 1 mysql mysql  157601 May 22  2013 mysqlbinlog

-rwxr-xr-x. 1 mysql mysql   10675 May 22  2013 mysqlbug

-rwxr-xr-x. 1 mysql mysql   26276 May 22  2013 mysqlcheck

-rwxr-xr-x. 1 mysql mysql  415234 May 22  2013 mysql_client_test

-rwxr-xr-x. 1 mysql mysql    6137 May 22  2013 mysql_config

-rwxr-xr-x. 1 mysql mysql    4169 May 22  2013 mysql_convert_table_format

-rwxr-xr-x. 1 mysql mysql   23253 May 22  2013 mysqld_multi

-rwxr-xr-x. 1 mysql mysql   16690 May 22  2013 mysqld_safe

-rwxr-xr-x. 1 mysql mysql   91933 May 22  2013 mysqldump

-rwxr-xr-x. 1 mysql mysql    6602 May 22  2013 mysqldumpslow

-rwxr-xr-x. 1 mysql mysql    3245 May 22  2013 mysql_find_rows

-rwxr-xr-x. 1 mysql mysql     483 May 22  2013 mysql_fix_extensions

-rwxr-xr-x. 1 mysql mysql    5894 May 22  2013 mysql_fix_privilege_tables

-rwxr-xr-x. 1 mysql mysql   31485 May 22  2013 mysqlhotcopy

-rwxr-xr-x. 1 mysql mysql   26643 May 22  2013 mysqlimport

-rwxr-xr-x. 1 mysql mysql   14623 May 22  2013 mysql_install_db

-rwxr-xr-x. 1 mysql mysql    7694 May 22  2013 mysql_secure_installation

-rwxr-xr-x. 1 mysql mysql   16689 May 22  2013 mysql_setpermission

-rwxr-xr-x. 1 mysql mysql   24611 May 22  2013 mysqlshow

-rwxr-xr-x. 1 mysql mysql   46541 May 22  2013 mysqlslap

-rwxr-xr-x. 1 mysql mysql  192366 May 22  2013 mysqltest

-rwxr-xr-x. 1 mysql mysql 1368006 May 22  2013 mysql_tzinfo_to_sql

-rwxr-xr-x. 1 mysql mysql   65042 May 22  2013 mysql_upgrade

-rwxr-xr-x. 1 mysql mysql  159255 May 22  2013 mysql_waitpid

-rwxr-xr-x. 1 mysql mysql    3818 May 22  2013 mysql_zap

-rwxr-xr-x. 1 mysql mysql 1388574 May 22  2013 perror

-rwxr-xr-x. 1 mysql mysql 1380024 May 22  2013 replace

-rwxr-xr-x. 1 mysql mysql 1379698 May 22  2013 resolveip

-rwxr-xr-x. 1 mysql mysql 1381612 May 22  2013 resolve_stack_dump

[root@centos6 bin]# ./mysqld_safe --user=root --skip-grant-tables&   /忽略授权表

[1] 2778

[root@centos6 bin]# 140523 18:55:56 mysqld_safe Logging to '/usr/local/mysql/var/centos6.err'.

140523 18:55:57 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var

[root@centos6 bin]# cd   /切换至root家目录

[root@centos6 ~]# mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 7

Server version: 5.1.42-log Source distribution

Copyright (c) 2000, 2012, 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>

/无需密码即可登录mysql数据库，通过前面的方法重新设置root用户新密码即可！

[root@centos6 bin]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.1.42-log Source distribution

Copyright (c) 2000, 2012, 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> select version();

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

| version()  |

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

| 5.1.42-log |

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

1 row in set (0.00 sec)

/查询mysql数据库版本

mysql> select user();

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

| user()         |

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

| root@localhost |

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

1 row in set (0.00 sec)

/查询当前登录用户

mysql> select user from mysql.user;

+-------+

| user  |

+-------+

| cacti |

| root  |

| root  |

|       |

| root  |

|       |

| cacti |

| root  |

| user1 |

+-------+

9 rows in set (0.00 sec)

mysql> show grants;

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

| Grants for root@localhost                                                                                                              |

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

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*D4CD521B67294775430C15B348B47051EBA4B810' WITH GRANT OPTION |

| GRANT ALL PRIVILEGES ON `cacti`.* TO 'root'@'localhost'                                                                                |

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

2 rows in set (0.00 sec)

/查看授权情况

mysql> grant select,insert,update,delete on *.* to zhang3@localhost identified by "zhang3";

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

/增加一个用户zhang3密码也为zhang3，让它可以从本机登录并对所有数据库有查询、插入、修改、删除权限

mysql> exit

[root@centos6 ~]# mysql -u zhang3 -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 448

Server version: 5.1.42-log Source distribution

Copyright (c) 2000, 2012, 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> select user();

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

| user()           |

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

| zhang3@localhost |

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

1 row in set (0.00 sec)

/新建用户zhang3登录数据库成功

二、如何封装源码安装的mysql为系统服务

[root@centos6 ~]# cd /usr/local/mysql/share/mysql/

[root@centos6 mysql]# ll

total 1268

-rw-r--r--. 1 mysql mysql   1153 May 22  2013 binary-configure

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 charsets

-rw-r--r--. 1 mysql mysql   3805 May 22  2013 config.huge.ini

-rw-r--r--. 1 mysql mysql   1661 May 22  2013 config.medium.ini

-rw-r--r--. 1 mysql mysql    905 May 22  2013 config.small.ini

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 czech

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 danish

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 dutch

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 english

-rw-r--r--. 1 mysql mysql 416006 May 22  2013 errmsg.txt

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 estonian

-rw-r--r--. 1 mysql mysql 591883 May 22  2013 fill_help_tables.sql

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 french

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 german

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 greek

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 hungarian

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 italian

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 japanese

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 korean

-rw-r--r--. 1 mysql mysql   5006 May 22  2013 mi_test_all

-rw-r--r--. 1 mysql mysql   1517 May 22  2013 mi_test_all.res

-rw-r--r--. 1 mysql mysql   4778 May 22  2013 my-huge.cnf

-rw-r--r--. 1 mysql mysql  20167 May 22  2013 my-innodb-heavy-4G.cnf

-rw-r--r--. 1 mysql mysql   4752 May 22  2013 my-large.cnf

-rw-r--r--. 1 mysql mysql   4763 May 22  2013 my-medium.cnf

-rw-r--r--. 1 mysql mysql   2401 May 22  2013 my-small.cnf

-rwxr-xr-x. 1 mysql mysql   1061 May 22  2013 mysqld_multi.server

-rw-r--r--. 1 mysql mysql  42249 May 22  2013 mysql_fix_privilege_tables.sql

-rw-r--r--. 1 mysql mysql    837 May 22  2013 mysql-log-rotate

-rwxr-xr-x. 1 mysql mysql  12302 May 22  2013 mysql.server

-rw-r--r--. 1 mysql mysql   1878 May 22  2013 mysql_system_tables_data.sql

-rw-r--r--. 1 mysql mysql  16527 May 22  2013 mysql_system_tables.sql

-rw-r--r--. 1 mysql mysql   9669 May 22  2013 mysql_test_data_timezone.sql

-rw-r--r--. 1 mysql mysql    589 May 22  2013 ndb-config-2-node.ini

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 norwegian

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 norwegian-ny

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 polish

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 portuguese

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 romanian

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 russian

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 serbian

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 slovak

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 spanish

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 swedish

drwxr-xr-x. 2 mysql mysql   4096 May 22  2013 ukrainian

[root@centos6 mysql]# cp mysql.server /etc/rc.d/init.d/mysql

[root@centos6 init.d]# chkconfig --list | grep mysql

[root@centos6 init.d]# chkconfig --add mysql

[root@centos6 init.d]# chkconfig --list | grep mysql

mysql           0:off   1:off   2:on    3:on    4:on    5:on    6:off

[root@centos6 init.d]# service mysql start

Starting MySQL....[  OK  ]

[root@centos6 init.d]# ps -ef | grep mysql

root      2184     1  0 23:34 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/var --pid-file=/usr/local/mysql/var/centos6.pid

mysql     2287  2184  0 23:34 pts/0    00:00:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --log-

error=/usr/local/mysql/var/centos6.err --pid-file=/usr/local/mysql/var/centos6.pid --socket=/tmp/mysql.sock --port=3306

root      2329  1849  0 23:35 pts/0    00:00:00 grep mysql

[root@centos6 init.d]# service mysql stop

Shutting down MySQL.[  OK  ]

[root@centos6 init.d]# ps -ef | grep mysql

root      2356  1849  0 23:35 pts/0    00:00:00 grep mysql

三、数据库的备份和恢复

[root@centos6 ~]# mysql -uroot -pwxsemico

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1218

Server version: 5.1.42-log Source distribution

Copyright (c) 2000, 2012, 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 |

| cacti              |

| mysql              |

| test               |

| ***                |

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

5 rows in set (0.00 sec)

mysql> use ***;

Database changed

mysql> show tables;

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

| Tables_in_*** |

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

| ***user       |

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

1 row in set (0.00 sec)

mysql> desc ***user;

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

| Field    | Type      | Null | Key | Default | Extra |

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

| name     | char(20)  | NO   | PRI | NULL    |       |

| password | char(128) | YES  |     | NULL    |       |

| active   | int(10)   | NO   |     | 1       |       |

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

3 rows in set (0.00 sec)

mysql> select * from ***user;

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

| name   | password                                  | active |

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

| user1  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |      1 |

| user2  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |      1 |

| user3  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |      1 |

| zhang3 | zhang123                                  |      1 |

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

4 rows in set (0.00 sec)

(1)、数据库***备份

root@centos6 ~]# mkdir /backup

[root@centos6 ~]# mysqldump -uroot -pwxsemico *** > /backup/***_bk.sql

[root@centos6 ~]# mysqldump -uroot -pwxsemico *** | gzip > /backup/***_bk.sql

[root@centos6 ~]# cd /backup

[root@centos6 backup]# ll

total 8

-rw-r--r-- 1 root root 776 May 24 03:05 ***_bk.sql

-rw-r--r-- 1 root root 776 May 24 03:08 ***_bk.sql.gz

/如若需要备份的数据库容量较大，可采用gzip压缩方式来减少容量

(2)、删除数据库***中***user表后恢复数据

mysql> drop table ***user;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from ***.***user;

ERROR 1146 (42S02): Table '***.***user' doesn't exist

mysql> exit

Bye

[root@centos6 ~]# mysql -u root -pwxsemico *** < /backup/***_bk.sql

[root@centos6 ~]# mysql -u root -pwxsemico

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1497

Server version: 5.1.42-log Source distribution

Copyright (c) 2000, 2012, 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 |

| cacti              |

| mysql              |

| test               |

| ***                |

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

5 rows in set (0.00 sec)

mysql> select * from ***.***user;

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

| name   | password                                  | active |

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

| user1  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |      1 |

| user2  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |      1 |

| user3  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |      1 |

| zhang3 | zhang123                                  |      1 |

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

4 rows in set (0.00 sec)

/数据库***中***user表及其数据恢复成功

(3)、直接删除***数据库后其数据恢复

[root@centos6 ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1558

Server version: 5.1.42-log Source distribution

Copyright (c) 2000, 2012, 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 |

| cacti              |

| mysql              |

| test               |

| ***                |

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

5 rows in set (0.00 sec)

mysql> drop database ***;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

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

| Database           |

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

| information_schema |

| cacti              |

| mysql              |

| test               |

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

4 rows in set (0.00 sec)

mysql> exit

Bye

[root@centos6 ~]# mysql -uroot -pwxsemico *** < /backup/***_bk.sql

ERROR 1049 (42000): Unknown database '***'

[root@centos6 ~]# mysql -uroot -pwxsemico

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1572

Server version: 5.1.42-log Source distribution

Copyright (c) 2000, 2012, 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> create database ***;

Query OK, 1 row affected (0.00 sec)

/删除***数据库后恢复数据库之前需先创建***数据库

mysql> exit

Bye

[root@centos6 ~]# mysql -uroot -pwxsemico *** < /backup/***_bk.sql

[root@centos6 ~]# mysql -u root -pwxsemico

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1580

Server version: 5.1.42-log Source distribution

Copyright (c) 2000, 2012, 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 |

| cacti              |

| mysql              |

| test               |

| ***                |

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

5 rows in set (0.00 sec)

mysql> select * from ***.***user;

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

| name   | password                                  | active |

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

| user1  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |      1 |

| user2  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |      1 |

| user3  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |      1 |

| zhang3 | zhang123                                  |      1 |

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

4 rows in set (0.00 sec)

/***数据库及其***user表中的数据恢复成功

四、其他相关操作

show status

/显示数据库的运行状态

show databases;

/显示数据库列表

use mysql;

/打开mysql数据库

show tables;

/显示mysql数据库中的数据表

describle 表名；

/显示数据表的结构

create database 库名；

/创建特定名称数据库

drop database 库名；

/删除特定名称数据库

create table 表名(字段设定列表)

/创建数据库中的特定表

drop table 表名；

/删除特定名称的表

delete from 表名；

/清空表记录

select * from 表名；

/显示表中的记录

insert into 表名 values(， ，)

/插入值到表中

alter table 表名 add column

/修改字段