MySQL常用命令

备份远程数据库

./mysqldump -h 'remote_ip' -usource_username -psource_password --opt --compress source_database --skip-lock-tables | mysql -h localhost -utarget_username -ptarget_password target_database

---

备份本地数据库

./mysqldump -usource_username -psource_password --opt --compress source_database --skip-lock-tables | mysql -utarget_username -ptarget_password target_database

---

删除指定库中的数据表 - 语句批量生成

SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'database_name';

---

mysql 导出数据库

./mysqldump -u username -p database -P 3306 > /data/test.sql

导出的sql在新的数据库中执行

mysql -uusername -p
use database;
source /path/xx.sql

---

mysql5.7用户表被清空后恢复
停止mysql服务
以safe模式进行重启

./mysqld_safe --skip-grant-tables

如果safe启动报错

在/usr/local/mysql创建链接文件

mkdir -p /usr/local/mysql/bin  
ln -s /home/mysql/product/5.6/mysql-1/bin/mysqld /usr/local/mysql/bin/mysqld  

然后无密码登录mysql：mysql -uroot

insert into user(host, user, authentication_string, ssl_cipher, x509_issuer, x509_subject) values('localhost', 'root', password('1234.abcd'), '', '', '');

下面这个是完整的用户权限

update user set Select_priv='y', Insert_priv='y', Update_priv='y', Delete_priv='y', Create_priv='y', Drop_priv='y', Reload_priv='y', Shutdown_priv='y', Process_priv='y', File_priv='y', Grant_priv='y', References_priv='y', Index_priv='y', Alter_priv='y', Show_db_priv='y', Super_priv='y', Create_tmp_table_priv='y', Lock_tables_priv='y', Execute_priv='y', Repl_slave_priv='y', Repl_client_priv='y', Create_view_priv='y', Show_view_priv='y', Create_routine_priv='y', Alter_routine_priv='y', Create_user_priv='y', Event_priv='y', Trigger_priv='y', Create_tablespace_priv='y' where user='username';