MySQL常用命令-[备忘]

=== backup a table data use msyqldump ===
bin/mysqldump -uxxxx -pxxx DB_Temp --tables T_Adapt_Format > /home/jacky/MyFiles/backup
如果只想导出数据库的结构，而不是所有的数据，请使用--no-data, -d这个选项。
=== 恢复mysqldump 备份的文件 ===
mysql -u root -p DB_Temp < /home/jacky/MyFiles/

=== 修改MySQL的root密码 ===
$mysqladmin -u root -p  password 'new-password'
=== 停止当前的MySQL服务 ===
$mysqladmin shutdown

=== 查看mysql 服务的运行选项,如data路径,配置文件的路径等. ===
# mysqld --help --verbose

或是登录到mysql服务器，运行命令：
show variables;

=== 授权用户除drop以外的基本权限. ===
GRANT ALTER,ALTER ROUTINE,CREATE,CREATE ROUTINE,CREATE VIEW,DELETE,EXECUTE,INDEX,INSERT,LOCK TABLES,SELECT,SHOW VIEW,UPDATE ON DB_Moqee.* TO 'xiaopaozi'@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'dummy' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON DB_XXX.* TO 'loginid'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
revoke drop ON DB_XXX..* from 'loginid'@'%';

=== 授权用户除drop以外的基本权限给所有的IP(5.0). ===
GRANT ALTER,ALTER ROUTINE,CREATE,CREATE ROUTINE,CREATE VIEW,DELETE,EXECUTE,INDEX,INSERT,LOCK TABLES,SELECT,SHOW VIEW,UPDATE ON DB_WAP.* TO 'WapUser'@'%' IDENTIFIED BY '1q2w3e4r' WITH GRANT OPTION;

=== 显示用户的权限 ===
SHOW GRANTS FOR 'xxx'@'123.456.789.001'
SHOW GRANTS FOR CURRENT_USER();

-----------查看所有的用户-----------
select User,Host from mysql.user;

=== 授予所有的权限 ===
GRANT ALL PRIVILEGES ON DB_xxxx.* TO 'root'@'xxx.xx.xxx.xx' IDENTIFIED BY 'somepassword' WITH GRANT OPTION;

FLUSH PRIVILEGES

-- 查看连接数
show processlist;

［打開查詢日志］
mysql> set global general_log='on';
mysql> set global general_log_file = '/tmp/mysql-query.log';


=== find out what operating parameters ===
shell> mysqladmin --host=HOST_NAME --port=PORT_NUMBER variables
=== Running multiple servers on unix. ===
shell>bin/mysqld_safe --datadir=/usr/local/share/mysql4/data --socket=/tmp/mysql4.sock --port=3308 --user=mysql&