为gaingreat添加test本地用户权限,密码123456
# 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
修改用户密码，有3种方法
# /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;
mysqldump导出数据库
# /usr/local/mysql/bin/mysqldump -uroot -p 数据库名 > /tmp/mysql_20121204.sql //导出整个数据库,包括数据，结构 # /usr/local/mysql/bin/mysqldump -uroot -p -d 数据库名 > /tmp/mysql_20121204.sql //只导出数据库结构，-d只导出结构 # /usr/local/mysql/bin/mysqldump -uroot -p 数据库名 表名 > /tmp/mysql_test_20121204.sql //导出一个表，包括表结构和数据 # /usr/local/mysql/bin/mysqldump -uroot -p -d 数据库名 表名 > /tmp/mysql_test_20121204.sql //只导出一个表结构，-d只导出结构 # /usr/local/mysql/bin/mysqldump -uroot -p --all-databases > /tmp/mysql_all_20140813.sql //导出所有数据库 # /usr/local/mysql/bin/mysqldump -uroot -p -d --all-databases > /tmp/mysql_all_20140813.sql //导出所有数据库，-d只导出结构 # /usr/local/mysql/bin/mysqldump -uroot -p --databases db1 db2 > db1db2.sql //同时导出db1,db2数据库 # /usr/local/mysql/bin/mysqldump -uroot -p db1 table1 table2 > tb1tb2.sql //同时导出db1的table1,table2 # /usr/local/mysql/bin/mysqldump -uroot -p -t --all-databases > /tmp/all.sql //导出所有数据库数据，-t只导出数据，不导出结构 # /usr/local/mysql/bin/mysqldump -uroot -p -t extmail mailbox --where="domain = 'coolnull.com'" > /home/coolnull/mailbox.20141020.sql //导出extmail库、mailx表中domain为coolnull.com的数据，不导出结构
source还原数据库
# /usr/local/mysql/bin/mysql -uroot -p < all.sql //导入所有数据库 # /usr/local/mysql/bin/mysql -uroot -p db1 < db1.sql; //导入db1数据库 或 # /usr/local/mysql/bin/mysql -uroot -p > create database db1 > use db1 > source /tmp/mysql_20121204.sql
查看数据库所有的表引擎
# /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 | |
清除root密码，实现mysql -S /tmp/mysql.sock，直接socket登录本机mysql
> 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;