Ubuntu MySQL 修改数据库路径 及 主从复制
环境：
阿里云 64位 Ubuntu Server 14
由于阿里云Linux系统仅赠送20G系统盘，存放数据是远远不够的。
额外购买一块数据盘，挂载为/mnt/xvdb1（用于存放MySQL数据）。挂载方式参考阿里云官方文档：
http://help.aliyun.com/knowledge_detail.htm?knowledgeId=5974154
缺省安装mysql-server-5.5
apt-get install mysql-server
或安装mysql-server-5.6
apt-get install mysql-server-5.6
〇、配置root从远程访问
vi /etc/mysql/my.cnf
#bind-address = 127.0.0.1
bind-address = 0.0.0.0
重新启动mysql：
service mysql restart
mysql -u root -p
mysql>use mysql;
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'rootpassword' WITH GRANT OPTION;
mysql>FLUSH PRIVILEGES;
一、先修改MySQL的数据存储路径
（从/var/lib/mysql到/mnt/xvdb1/database/var-lib-mysql）：
停止mysql：
service mysql stop
mkdir /mnt/xvdb1/database
cp -r /var/lib/mysql /mnt/xvdb1/database/var-lib-mysql
chown -R mysql:mysql /mnt/xvdb1/database/var-lib-mysql
修改mysql配置（主从相同）：（参考：http://www.linuxidc.com/Linux/2015-01/112587.htm）
vi /etc/mysql/my.cnf
[mysqld]
#datadir = /var/lib/mysql
datadir = /mnt/xvdb1/database/var-lib-mysql
vi /etc/apparmor.d/usr.sbin.mysqld
#/var/lib/mysql/ r,
#/var/lib/mysql/** rwk,
/mnt/xvdb1/database/var-lib-mysql/ r,
/mnt/xvdb1/database/var-lib-mysql/** rwk,
重启apparmor和mysql，并检查：
/etc/init.d/apparmor restart
service mysql start
mysql -uroot -p
mysql> show variables like '%dir%';
显示 datadir 为 /mnt/xvdb1/database/var-lib-mysql即可。
二、配置mysql主从复制
参考：https://www.centos.bz/2011/07/linux-mysql-replication-sync-configure/
http://xuwensong.elastos.org/2014/01/07/ubuntu-%E4%B8%8B-mysql-%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6%E5%8F%8Amysql-proxy-%E8%AF%BB%E5%86%99%E5%88%86%E7%A6%BB/
假设：
主服务器内网IP：10.0.0.1
从服务器内网IP：10.0.0.2
主服务器需要将名称为mydb的数据库实时复制到从服务器，用于备份或者读写分离的读服务器。
创建数据库及部分命令，我使用navicat for mysql，过程不表。
还是先停止mysql服务
service stop msyql
主服务器：
vi /etc/mysql/my.cnf
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
bind-address = 0.0.0.0 #主服务器需要被从服务器通过网络访问
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
# my replication settings
server-id = 1 #主服务器id为1#log_bin = /var/log/mysql/mysql-bin.log
# 特别注意：mysql复制所产生的binary日志特别大，如果放在/var/log/mysql/目录下会导致阿里云缺省20G的系统盘空间迅速吃满，并导致数据库不可用
log_bin = /mnt/xvdb1/database/mysql_repl_log/mysql-bin.log
#innodb_flush_log_at_trx_commit = 1 #不明白，所以注释了
sync_binlog = 1
expire_logs_days = 10
max_binlog_size = 100M
binlog_format = mixed
binlog_do_db = mydb #需要复制的数据库名称
binlog_ignore_db = mysql,information_schema,performance_schema #不需要复制的系统数据库
注：binlog_format=mixed是为了解决Hibernate异常：Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
参考： https://confluence.atlassian.com/pages/viewpage.action?pageId=251724630
http://dba.stackexchange.com/questions/58459/mysql-error-impossible-to-write-to-binary-log
启动mysql
service mysql start
mysql -uroot -p
mysql> CREATE USER 'repl'@'10.0.0.2' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.2' IDENTIFIED BY 'password';
mysql> show master status;
+------------------+----------+--------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000007 | 15267 | mydb | mysql,information_schema,performance_schema |
+------------------+----------+--------------+---------------------------------------------+
1 row in set (0.00 sec)
从服务器
在开始配置从服务器前，需要将主服务器的数据库mydb数据完整的导入从服务器，我使用navicat for mysql，右键单击数据库，选择Data Transfer工具完成导入。
vi /etc/mysql/my.cnf
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
# my replication settings
server-id = 2 #从服务器id为2#log_bin = /var/log/mysql/mysql-bin.log
# 特别注意：mysql复制所产生的binary日志特别大，如果放在/var/log/mysql/目录下会导致阿里云缺省20G的系统盘空间迅速吃满，并导致数据库不可用
log_bin = /mnt/xvdb1/mysql_repl_log/mysql-bin.log
binlog_format = mixed
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
mysql -uroot -p
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.1',MASTER_USER='repl',MASTER_PASSWORD='password',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000007',MASTER_LOG_POS=15267,MASTER_CONNECT_RETRY=10;
特别注意：
此处的MASTER_LOG_FILE和MASTER_LOG_POS必须与主服务器查出来的值一致。由于忽略了此处，导致走了弯路。
从服务器一直报错。参考：http://www.percona.com/blog/2014/05/14/max_allowed_packet-and-binary-log-corruption-in-mysql/
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master'
事实是MASTER_LOG_POS与主服务器不相符，而与max_allowed_packet无关。
当然，max_allowed_packet缺省值为16M，根据实际需要改为了32M.
mysql> start slave;
（关闭指令为stop slave;）
mysql> show slave status\G;
如果Slave_IO_Running和Slave_SQL_Running的值同时为Yes，则复制配置成功。否则需要查看 Last_Error 解决问题。
测试在主服务器写入或修改数据，从服务器几乎同步发生变化。
MySQL备份与恢复
参考：http://www.oicto.com/mysqldump-beifenhuifu/1、备份：
mysqldump -uroot -p dbname > dbname.sql
2、忽略某张表备份：
mysqldump -uroot -p dbname --ignore-table=dbname.tablename > dbname.sql
3、备份并gzip压缩： mysqldump -uroot -p dbname | gzip > dbname.sql.gz
4、恢复：
mysql -uroot -p dbname < dbname.sql