一天一点点(20151217）

主题: mysqladmin — Client for Administering a MySQL Server
（来自MySQL reference manual 4.5.2）
=============================================
可以使用mysqladmin来检查server的配置和运行状态，创建和删除数据库等等；
shell> mysqladmin [options] command [command-arg] [command [command-arg]] ...
mysqladmin支持下面的命令，部分命令后面可以跟参数
create db_name,drop db_name
debug
extended-status
flush-hosts
flush-logs
kill id,id,...
password new_password
所有命令能使用前缀简写形式
shell> mysqladmin proc stat
+----+-------+-----------+----+---------+------+-------+------------------+
| Id | User  | Host      | db | Command | Time | State | Info             |
+----+-------+-----------+----+---------+------+-------+------------------+
| 51 | monty | localhost |    | Query   | 0    |       | show processlist |
+----+-------+-----------+----+---------+------+-------+------------------+
Uptime: 1473624  Threads: 1  Questions: 39487
Slow queries: 0  Opens: 541  Flush tables: 1
Open tables: 19  Queries per second avg: 0.0268


主题: mysqldump — A Database Backup Program
（来自MySQL reference manual 4.5.4）
=============================================
mysqldump命令能够输出csv、xml文本或者其他分隔符的文本格式文件；
mysqldump至少需要select/show view/trigger/lock table等权限，某些特殊选项可能需要特殊权限；
重新加载数据需要执行这些语句的权限，比如create、alter权限；
windows平台dump文件的话可能使用的是powershell，他dump出的文件使用的是utf-16的编码，但是utf-16不能作为连接字符集，因此dump文件无法正确的加载。为了解决这个问题，使用--result-file选项，使得dump以ASCII格式输出文件。
shell> mysqldump [options] > dump.sql
shell> mysqldump [options] --result-file=dump.sql

基于性能和可扩展性的方案
使用mysqldump备份的优势在于恢复前可以编辑dump文件，查看内容方便灵活；可以用这种办法dump数据，然后做些简单的修改，以用于测试；
如果备份的数据量比较大，而且要快速的备份，使用mysqldump是不合适的；就算备份时间还能容忍，其恢复速度也是相当漫长，因为重放sql插入数据会产生大量的磁盘I/O，索引排序等。

对大规模的备份恢复来说，使用物理备份是更为明智的选择，因为他直接复制文件而且恢复很快
1.如果你的数据库的表主要是InnoDB引擎的，或者InnoDB、MyISAM两种皆有，那么MySQl企业版备份工具mysqlbackup是一个不错的选择；
2.mysqlhotcopy

mysqldump能够逐行的提取并dump表的数据，也能提取整个表的数据并缓存到内存中，然后dump出整个表的数据；如果表比较大，缓存导出可能会出现问题，默认是逐行导出，如果要缓存导出的话可以加--skip-quick选项；

如果想把新版本的库传输到老版本的库，不要使用--opt选项，可以使用--skip-opt或者--extended-insert选项。

特殊选项
--allow-keywords
--apply-slave-statements
--comments
--compact
--compatible
--compress
--create-options
--delayed-insert
--disable-keys
--dump-date  需同时有--comment选项
--dump-slave  *****搭建复制重要******
--extended-insert  多行插入
--flush-logs   dump前刷日志
--hex-blob  用十六进制法导出二进制列
--include-master-host-port  --需同时有--dump-slave选项
--master-data
--no-data  只导出数据库对象
--set-gtid-purged
--single-transaction
--tab
--where

Connection Options
Option-File Options
DDL Options
Debug Options
Help Options
Internationalization Options
Replication Options
Format Options
Filtering Options
Performance Options
Transactional Options
Option Groups

shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name
shell> mysqldump --all-databases --master-data=2 > all_databases.sql
shell> mysqldump --all-databases --flush-logs --master-data=2
              > all_databases.sql

日志轮转
You can even do your mysqldump backups with logrotate.
Simply put something like this into /etc/logrotate.conf:

/var/backups/mysql/dump.sql {
daily
rotate 14
missingok
compress
postrotate
/usr/bin/mysqldump --defaults-extra-file=/.../backup-credentials.cnf --opt --flush-logs --all-databases > /var/backups/mysql/dump.sql
endscript
}

加速恢复
Following mysqldump import example for InnoDB tables is at least 100x faster than previous examples.

1. mysqldump --opt --user=username --password database > dumbfile.sql

2. Edit the dump file and put these lines at the beginning:

SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;

3. Put these lines at the end:

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;

4. mysql --user=username --password database < dumpfile.sql


备份压缩改名并上传ftp
If you want to schedule a task on windows to backup and move your data somewhere, the lack of documentation and command-line tools in windows can make it a real beast. I hope this helps you keep your data safe.

First off, you will need a command line file compressor (or your should use one, anyway). I like GNU gzip. You can get it for windows here

Secondly, you will need to use windowsw FTP via command line. It took me all day to find documentation on this guy, so I hope this saves some time for somebody.

Anyway, you need two files -- the batch file and a script for your ftp client. The Batch file should look like this guy (it uses random numbers in the file name so that multiple backups are not overwritten):

@ECHO OFF

@REM Set dir variables. Use ~1 format in win2k
SET basedir=C:\BACKUP~1
SET workdir=c:\TEMP
SET mysqldir=c:\mysql\bin
SET gzipdir=c:\PROGRA~1\GnuWin32\bin
SET mysqlpassword=mygoodpassword
SET mysqluser=myrootuser

@REM Change to mysqldir
CD %mysqldir%

@REM dump database. This is all one line
mysqldump -u %mysqluser% -p%mysqlpassword% --all-databases >%workdir%\backup.sql

@REM Change to workdir
CD %workdir%

@REM Zip up database
%gzipdir%\gzip.exe backup.sql

@REM Move to random file name
MOVE backup.sql.gz backup.%random%.gz

@REM FTP file to repository
FTP -n -s:%basedir%\ftp-commands.txt

@REM Remove old backup files
del backup.sql
del backup.*.gz

@REM Change back to base dir
CD %basedir%

And your ftp script should look like this guy (and be named ftp-commands.txt so the above script can find it)

open
ftp.mybackuplocation.com
user
myusername
mypassword
bin
put backup.*.gz
quit

Make sure both of the above files are in whatever directory you set up as %basedir% and test it out and make sure everything works for you. Then schedule it to run every day to protect your data!



===============================
Corey's example is helpful, but I don't care for the random file name. Here is the manual script I use on Windows for kicking off a MYSQL backup.

You could easily add all the other bells and whistles of ZIP, FTP, and scheduling should you need it. Note that I didn't use a password or many of the other args for mysqldump, you can add those if ya need 'em.

@ECHO OFF
for /f "tokens=1-4 delims=/ " %%a in ('date/t') do (
set dw=%%a
set mm=%%b
set dd=%%c
set yy=%%d
)

SET bkupdir=C:\path\to\where\you\want\backups
SET mysqldir=D:\path\to\mysql
SET dbname=this_is_the_name_of_my_database
SET dbuser=this_is_my_user_name

@ECHO Beginning backup of %dbname%...

%mysqldir%\bin\mysqldump -B %dbname% -u %dbuser% > %bkupdir%\dbBkup_%dbname%_%yy%%mm%%dd%.sql
@ECHO Done! New File: dbBkup_%dbname%_%yy%%mm%%dd%.sql
pause
==================================================

Here's a bash wrapper for mysqldump I cron'd to run at night. It's not the sexiest thing but it's reliable.

It creates a folder for each day, a folder for each db & single bzip2'd files for each table. There are provisions for exclusions. See below where it skips the entire tmp & test db's and in all db's, tables tbl_session & tbl_parameter. It also cleans up files older than 5 days (by that time they've gone to tape).

Be sure to update & . Ideally these would be in constants but I couldn't get the bash escaping to work.

# setup
suffix=`date +%Y%m%d`
dest=/mirror/mysqldumps
cmd='/usr/bin/mysqldump'

databases=(`echo 'show databases;' | mysql -u --password='' | grep -v ^Database$`)

for d in "${databases[@]}"; do
if [[ $d != 'tmp' && $d != 'test' ]]
then
echo "DATABASE ${d}"
s="use ${d}; show tables;"
tables=(`echo ${s} | mysql -u --password='' | grep -v '^Tables_in_'`)
for t in "${tables[@]}"; do
if [[ $t != 'tbl_parameter' && $t != 'tbl_session' ]]
then
echo " TABLE ${t}"
path="${dest}/${suffix}/${d}"
mkdir -p ${path}
${cmd} --user= --password='' --quick --add-drop-table --all ${d} ${t} | bzip2 -c > ${path}/${t}.sql.bz2
fi
done
fi
done

# delete old dumps (retain 5 days)
find ${dest} -mtime +5 -exec rm {} \;

=========================================