Linux环境中MySQL主从同步--添加新的从库

当前我认为数据库主从有两大应用价值：1.从库相当于主库的备份。虽然数据库的主从并不能代替/取代备份，例如错误的数据可能毁掉所有的数据库，但主从也是在一种可读的状态下保持备份的一种实现方式。2.从库可以缓解主库的压力，能提高性能。由于从库是只读的，因此在读取查询方面，从库可以代替主库，承担一定的压力，无论是这些压力是来自用户（应用程序）还是开发、运维自己。MySQL的主从其实并不困难，而且需要做的事情也并不多，mysql官方给出的文档中对主从也做出了详细的指导、说明和解释，但总共也多少篇幅。鉴于很多网友已经详细叙写了相关的文档或文章，此处就能不赘述了（也可以略微参考本文的最后一部分“数据库主从的配置过程中使用的一些命令和步骤”）。简单说几句关于添加新的从库的小技巧。因为添加从库时，需要知道MASTER_LOG_FILE和MASTER_LOG_POS，虽然通过在master主库上执行“show master status”能看到当前的MASTER_LOG_FILE和MASTER_LOG_POS，但实际上这种方法只能用于master主库刚开始用的情况下，即数据库刚建好，没有任何数据写入的情况下，或者能将这些数据先手动同步到从库中去。一种较为方便的方法就是，通过mysqldump导出主库的数据库，同时导出事件、触发器、函数和视图，以及master-data，这样通过查看mysqldump导出的文件的前几行就能找到MASTER_LOG_FILE和MASTER_LOG_POS，这时在将从主库导出的sql语句在从库执行以后，就可以通过这一个MASTER_LOG_FILE和MASTER_LOG_POS，配置好从库，并开始同步。关于mysqldump的详细说明以及MySQL数据库的的备份脚本可以参考文章《Linux Shell脚本之利用mysqldump备份MySQL数据库（详细注解）》以及文章中的评论。通过mysqldump导出主库的数据库，同时导出事件、触发器、函数和视图，以及master-data：/usr/local/mysql/bin/mysqldump -uusername -hipaddress -ppassword -Pport --routines --events --triggers --single-transaction --flush-logs --master-data=1 –databases databasename如果想单独导出事件、触发器、函数和视图等而不导出数据，则可以执行：/usr/local/mysql/bin/mysqldump -uusername -hipaddress -ppassword -Pport --routines --events --triggers --no-create-info --no-data --no-create-db此外将一个数据库主从的配置过程中使用的一些命令和步骤陈列如下，以作参考或备忘：# Sat Aug 22 11:23:03 CST 2015
# Get mysql 5.5.38 for production use
# https://downloads.mariadb.com/archive/index/p/mysql/v/5.5.38
# https://downloads.mariadb.com/archives/mysql-5.5/mysql-5.5.38.tar.gz
wget -c https://downloads.mariadb.com/archives/mysql-5.5/mysql-5.5.38-linux2.6-x86_64.tar.gz

# Remove some conflicts
rpm -e mysql-server mysql
userdel mysql
rm -rf /usr/local/mysql/
rm -f /etc/my.cnf

# Set user and group
groupadd -r -g 27 mysql
useradd -r -u 27 -g mysql mysql -c "MySQL Server" -d /dev/null -s /sbin/nologin

groupadd -r mysql
useradd -r -g mysql mysql -c "MySQL Server" -d /dev/null -s /sbin/nologin

# Install MySQL database
tar zxf mysql-5.5.38-linux2.6-x86_64.tar.gz
cp -r mysql-5.5.38-linux2.6-x86_64 /usr/local/mysql
# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --explicit_defaults_for_timestamp --skip-name-resolve
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql/data --user=mysql --skip-name-resolve

# Run it temporarily
# /usr/local/mysql/bin/mysqld_safe &
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

# Set mysql commands
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
ln -s /usr/local/mysql/bin/mysqladmin /usr/bin/mysqladmin
ln -s /usr/local/mysql/bin/mysqldump /usr/bin/mysqldump
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /usr/local/mysql/bin/mysql_config /usr/bin/mysql_config

# set mysql root password, etc
/usr/local/mysql/bin/mysql_secure_installation

# Set mysql library
ln -s /usr/local/mysql/lib/libmysqlclient.so.18.0.0 /usr/lib64/libmysqlclient.so.18.0.0
ln -s /usr/local/mysql/lib/libmysqlclient.so.18.0.0 /usr/lib64/libmysqlclient.so.18
ln -s /usr/local/mysql/lib/libmysqlclient.so.18.0.0 /usr/lib64/libmysqlclient.so
ls -l /usr/lib64/libmysqlclient.so.18.0.0
ls -l /usr/lib64/libmysqlclient.so.18
ls -l /usr/lib64/libmysqlclient.so
vim /etc/ld.so.conf.d/mysql-x86_64.conf
/usr/lib64/mysql
/usr/local/mysql/lib
ldconfig

# Set mysql replication
# master db
cp /usr/local/mysql/support-files/my-small.cnf /etc/my.cnf
sed -i "s@server-id = 1@server-id = 101@g" /etc/my.cnf
sed -i "s@#log-bin=mysql-bin@log-bin=mysql-bin@g" /etc/my.cnf
sed -i "s@#binlog_format=mixed@binlog_format=mixed@g" /etc/my.cnf
# slave db
cp /usr/local/mysql/support-files/my-small.cnf /etc/my.cnf
sed -i "s@server-id = 1@server-id = 103@g" /etc/my.cnf

# read errors from logs
cat /usr/local/mysql/data/`hostname`.err

# Some reference
# server-id = 101
# log-bin=/usr/local/mysql/data/bin-log
# max_binlog_size = 1500M
# binlog_cache_size = 128K
# binlog-do-db = devdbops
# binlog-ignore-db = mysql
# log-slave-updates
# expire_logs_day=2
# binlog_format=mixed

# end mysql temporarily
kill -TERM `ps -ef | awk "/mysqld_safe/ && ！ /awk/ {print $2}"` || kill -KILL `ps -ef | awk "/mysqld_safe/ && ！ /awk/ {print $2}"`
ps -ef | awk "/mysqld_safe/ && ！ /awk/ {print $2}"
kill -TERM `ps -ef | awk "/mysqld/ && ！ /awk/ {print $2}"` || kill -KILL `ps -ef | awk "/mysqld/ && ！ /awk/ {print $2}"`
ps -ef | awk "/mysqld/ && ！ /awk/ {print $2}"

# Setting sysvinit
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql

# Start mysql database
service mysql start
service mysql status

# some operation about replicaion
# master db
GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root";
FLUSH PRIVILEGES;
CREATE USER "dev"@"%" IDENTIFIED BY "dev";
CREATE DATABASE IF NOT EXISTS devdbops;
GRANT ALTER,ALTER ROUTINE,CREATE,CREATE ROUTINE,CREATE TEMPORARY TABLES,CREATE VIEW,DELETE,DROP,EXECUTE,INDEX,INSERT,LOCK TABLES,SELECT,UPDATE,SHOW VIEW ON devdbops.* TO "dev"@"%";
USE devdbops;
CREATE TABLE `testtable` （
`id` int NOT NULL ,
`name` varchar（255） NULL ,
`value` varchar（255） CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
PRIMARY KEY （`id`）
）
;
INSERT INTO `testtable` （`id`, `name`, `value`） VALUES （"0", "a", "b"）;
# CREATE USER "repl"@"%.mydomain.com" IDENTIFIED BY "slavepass";
# GRANT REPLICATION SLAVE ON *.* TO "repl"@"%.mydomain.com";
# CREATE USER "repl"@"%" IDENTIFIED BY "slavepass";
# GRANT REPLICATION SLAVE ON devdbops.* to "replication"@"%";
SHOW MASTER STATUS;
SHOW PROCESSLIST G;
SHOW SLAVE HOSTS;
QUIT;

# slave db
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST="192.168.1.101",MASTER_USER="root",MASTER_PASSWORD="root",MASTER_LOG_FILE="mysql-bin.000002",MASTER_LOG_POS=107;
START SLAVE;
SHOW SLAVE STATUS G;

# Read for testtag:mysql主从配置,mysql添加从库,mysql备份视图函数,mysql备份脚本,Linux安装mysql--end--通过 XtraBackup 实现不停机不锁表搭建MySQL主从同步 http://www.linuxidc.com/Linux/2015-08/121806.htmUbuntu下Nginx做负载实现高性能WEB服务器5---MySQL主主同步 http://www.linuxidc.com/Linux/2012-06/61687p5.htm生产环境MySQL主主同步主键冲突处理 http://www.linuxidc.com/Linux/2013-07/86890.htmMySQL主从失败错误Got fatal error 1236 http://www.linuxidc.com/Linux/2012-02/54729.htmMySQL主从复制，单台服务器上实施 http://www.linuxidc.com/Linux/2013-03/81913.htm搭建MySQL代理服务器实现读写分离+主从同步 http://www.linuxidc.com/Linux/2014-05/102265.htmMySQL 5.5 主从双向同步 http://www.linuxidc.com/Linux/2012-12/75973.htmMySQL 5.5主从同步排错 http://www.linuxidc.com/Linux/2014-08/105416.htmMySQL主从复制异步半同步实例 http://www.linuxidc.com/Linux/2014-10/107608.htm本文永久更新链接地址