mysql

备份，还原

 

mysqldump

备份test数据库(--opt --quick -C)

/usr/local/mysql/bin/mysqldump -uroot -p --flush-logs test|gzip -9>test.sql.gz 

还原test数据库(-C)

/usr/local/mysql/bin/mysql -uroot -p << !mysql_shell!
create database test
exit
!mysql_shell!
gzip <test.sql.gz|/usr/local/mysql/bin/mysql -uroot -p

备份所有数据库(opt --quick -C)

/usr/local/mysql/bin/mysqldump -uroot -p --all-databases|gzip -9>all.sql.gz

还原所有数据库(-C)

gzip <all.sql.gz|/usr/local/mysql/mysql -uroot -p

mysql命令行

查看数据库（默认）引擎

mysql> show engines;

查看表引擎（test）

use test;
show variables like '%storage_engine%;

错误

Starting php_fpm /usr/local/php/bin/php-cgi: error while loading shared libraries: libmysqlclient.so.16: cannot open shared object file: No such file or directory

ln -s libmysqlclient.so.16.0.0 /usr/lib64/libmysqlclient.so

Table 'mysql.plugin' doesn't exist

/usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql/data --user=mysql

mqldump: Got error: 145: Table './recurser/wp_options' is marked as crashed and should be repaired when using LOCK TABLES

myisamchk -r wp_options

mv cond_instances.frm cond_instances.frm.bak

mqldump: Got error: 1017: Can't find file: 'cond_instances' (errno: 2) when using LOCK TABLES

mv cond_instances.frm cond_instances.frm.bak

链接

mysqldump参数详解