备份远程数据库
./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';
