三木的人生——3mu.me

kill -9 `ps -ef|grep mysql|grep -v grep|awk '{print $2}'|xargs`;
ps -ef|grep mysql

参考资料：
关于 mysql 重启不读取 /etc/my.cnf 全局配置的问题：http://my.oschina.net/leejun2005/blog/76193

备份MySQL数据库的命令(导出整个数据库,包括数据,结构)
# mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql

备份MySQL数据库为带删除表的格式
备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。
# mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql

直接将MySQL数据库压缩备份
# mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

备份MySQL数据库某个(些)表,包括表结构和数据
# mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

同时备份多个MySQL数据库
# mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql

仅仅备份数据库结构
# mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql

备份服务器上所有数据库
# mysqldump –all-databases > allbackupfile.sql

只导出数据库结构，-d只导出结构
# /usr/local/mysql/bin/mysqldump -uroot -p -d 数据库名 > /tmp/mysql_20121204.sql

只导出一个表结构，-d只导出结构
# /usr/local/mysql/bin/mysqldump -uroot -p -d 数据库名 表名 > /tmp/mysql_test_20121204.sql

导出所有数据库，-d只导出结构
# /usr/local/mysql/bin/mysqldump -uroot -p -d --all-databases > /tmp/mysql_all_20140813.sql

导出所有数据库数据，-t只导出数据，不导出结构
# /usr/local/mysql/bin/mysqldump -uroot -p -t --all-databases > /tmp/all.sql

source还原数据库

还原MySQL数据库的命令,导入databasename数据库 
# mysql -hhostname -uusername -ppassword databasename < backupfile.sql
或
# /usr/local/mysql/bin/mysql -uroot -p
> create database databasename
> use databasename
> source /tmp/mysql_20121204.sql

导入所有数据库
# /usr/local/mysql/bin/mysql -uroot -p < all.sql

还原压缩的MySQL数据库
# gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

将数据库转移到新服务器
# mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename

参考资料：
MySQL数据库备份命令：http://www.cnblogs.com/acpp/archive/2010/01/06/1640542.html

# mysql -e "show databases;" -uroot -p| grep -Ev "Database|information_schema|mysql|test|performance_schema" | xargs mysqldump -uroot -p --databases > mysql_dump.sql

# grant all privileges on gaingreat.* to test@localhost identified by '123456';

查看用户权限：

> show grants for username@localhost;
或
> select * from mysql.user where user='username';

收回权限

> revoke all on gaingreat.* from test@'192.168.1.212'  #假设ip为192.168.1.212

# /usr/local/mysql/bin/mysqladmin -uroot -p password '123456'
Enter password: 旧密码
或
> use mysql;
> SET PASSWORD FOR root=PASSWORD('new password');
或
> use mysql;
> UPDATE user SET password=PASSWORD("new password") WHERE user='root';

> show create database dbname;

查看创建表时用到的参数

> show create table tickets;

> RENAME TABLE USER TO user;

# /usr/local/mysql/bin/mysql -u test -p
> use db1
> show table status;
+--------------------------------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+--------------------------------------------------------------+
| Name                           | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment                                                      |
+--------------------------------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+--------------------------------------------------------------+
| cdef                           | MyISAM |      10 | Dynamic    |   15 |             67 |        1016 |   281474976710655 |         2048 |         0 |             25 | 2013-06-24 19:44:05 | 2013-06-25 15:31:08 | NULL                | latin1_swedish_ci |     NULL |                |                                                              |
| cdef_items                     | MyISAM |      10 | Dynamic    |   37 |             59 |        2192 |   281474976710655 |         3072 |         0 |             45 | 2013-06-24 19:44:05 | 2013-06-25 15:31:08 | NULL                | latin1_swedish_ci |     NULL |                |

> use mysql;
> select Host,User,Password from user where User='root';
> update user set password=password("") where user='root' and Host='localhost';   //修改localhost的root密码为空
> select Host,User,Password from user where User='root';
> flush privileges;  //或/etc/init.d/mysql restart重启
# mysql -S /tmp/mysql.sock  //这样就可以直接mysql.sock登录本机mysql

mysql> select database();
+------------+
| database() |
+------------+
| percona    |
+------------+
1 row in set (0.00 sec)
或者status;