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

浏览数：24 / 时间：2015年06月12日

一、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@wxsemico password wxsemico

/红色原密码，蓝色新密码
[root@centos6 ~]# mysql -u root -pwxsemico
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 386
Server version: 5.1.42-log 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>

/使用新密码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

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

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

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

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 vpn;
Database changed
mysql> show tables;
+---------------+
| Tables_in_vpn |
+---------------+
| vpnuser |
+---------------+
1 row in set (0.00 sec)

(1)、数据库vpn备份
root@centos6 ~]# mkdir /backup
[root@centos6 ~]# mysqldump -uroot -pwxsemico vpn > /backup/vpn_bk.sql
[root@centos6 ~]# mysqldump -uroot -pwxsemico vpn | gzip > /backup/vpn_bk.sql
[root@centos6 ~]# cd /backup
[root@centos6 backup]# ll
total 8
-rw-r--r-- 1 root root 776 May 24 03:05 vpn_bk.sql
-rw-r--r-- 1 root root 776 May 24 03:08 vpn_bk.sql.gz
/如若需要备份的数据库容量较大，可采用gzip压缩方式来减少容量

(2)、删除数据库vpn中vpnuser表后恢复数据
mysql> drop table vpnuser;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from vpn.vpnuser;
ERROR 1146 (42S02): Table ‘vpn.vpnuser‘ doesn‘t exist
mysql> exit
Bye
[root@centos6 ~]# mysql -u root -pwxsemico vpn < /backup/vpn_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

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.

(3)、直接删除vpn数据库后其数据恢复
[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

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> drop database vpn;
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye
[root@centos6 ~]# mysql -uroot -pwxsemico vpn < /backup/vpn_bk.sql
ERROR 1049 (42000): Unknown database ‘vpn‘
[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

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 vpn;
Query OK, 1 row affected (0.00 sec)
/删除vpn数据库后恢复数据库之前需先创建vpn数据库
mysql> exit
Bye
[root@centos6 ~]# mysql -uroot -pwxsemico vpn < /backup/vpn_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

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.

四、其他相关操作

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 <字段名><字段选项>
/修改字段