由于业务需要在Mysql实例中创建部分库的从库，已有的Mysql实例的版本是mysql-5.5.49，是一个非常老的版本。
本文档涉及到服务器中运行多实例和构建实例中部分库的从库。
| 服务器 | mysql端口 | 功能 |
| 服务器1 | 3306 | 主库 |
| 服务器2 | 3307 | 服务器1中部分库的从库 |
1、服务器2上创建3307实例
首先需要准备源码编译包，这个就不在描述了。由于我服务器2上已经存在了一个同样版本的实例，我就直接copy一份，命名为： mysql-5.5.49_3307 ，并做了一个软连接
ln -s mysql-5.5.49_3307 mysql_3307
服务器2的3307实例是从库，my.cnf的配置最好服务器1主库实例的配置相同，注意修改对应server_id
[client]
port =
socket = /tmp/mysql_3307.sock [mysqld]
port =
socket = /tmp/mysql_3307.sock
datadir = /opt/mysql_3307
tmpdir=/tmp skip-name-resolve
log_warnings = #skip-grant-tables
big_tables = on
back_log =
max_connections =
max_connect_errors =
table_open_cache =
max_allowed_packet = 128M
binlog_cache_size = 4M
max_heap_table_size = 1024M
read_buffer_size = 16M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 16M
thread_cache_size =
thread_concurrency =
query_cache_size = 512M
query_cache_limit = 32M
default-storage-engine = MYISAM
#thread_stack = 192K
#transaction_isolation = REPEATABLE READ
tmp_table_size = 1024M server-id =
log-bin = mysql-bin
binlog_format = mixed
expire_logs_days = replicate_wild_ignore_table = performance_schema.%
replicate_wild_ignore_table = information_schema.%
replicate_wild_ignore_table = mysql.%
replicate_wild_ignore_table = test.%
replicate_wild_ignore_table = tmp.%
#skip-name-resolve slave_net_timeout =
#master-connect-retry = log-slave-updates = slow_query_log = sql-slow.log
long_query_time =
slave-skip-errors = , key_buffer_size = 2G
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 4G
myisam_repair_threads =
myisam_recover innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 4G
innodb_data_file_path = ibdata1:256M:autoextend
innodb_write_io_threads =
innodb_read_io_threads =
innodb_thread_concurrency =
innodb_flush_log_at_trx_commit =
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group =
innodb_max_dirty_pages_pct =
innodb_lock_wait_timeout =
innodb_file_per_table = [mysqldump]
quick
max_allowed_packet = 32M [mysql]
no-auto-rehash [myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M [mysqlhotcopy]
interactive-timeout [mysqld_safe]
open-files-limit =
#log-error=/opt/mysql_3307/mysqld.log
long_query_time =
log-slow-queries=/opt/mysql_3307/mysql_slow_query.log
注意 添加忽略到不需要同步的主库，（涉及隐私就贴部分公共库出来了）
replicate_wild_ignore_table = performance_schema.%
replicate_wild_ignore_table = information_schema.%
replicate_wild_ignore_table = mysql.%
replicate_wild_ignore_table = test.%
replicate_wild_ignore_table = tmp.%
创建数据目录，目录名称一定和my.cnf文件中 “datadir” 配置相同
mkdir -p /opt/mysql_3307
初始化数据库
/usr/local/mysql_3307/scripts/mysql_install_db --defaults-file=/etc/my_3307.cnf --user=mysql --basedir=/usr/local/mysql-5.5.49_3307/ --datadir=/opt/mysql_3307/
启动数据库
nohup /usr/local/mysql_3307/bin/mysqld_safe --defaults-file=/etc/my_3307.cnf &
登录设置root密码，第一次登陆不需要输入密码
#/usr/local/mysql_3307/bin/mysql -uroot -S /tmp/mysql_3307.sock
mysql>update mysql.user set password=PASSWORD('123456') where User='root';
mysql>flush privileges;
2、服务器1上导出实例3306的部分库（需要做从库的数据库）
本次需要对 wcd_ft 这个数据库做从库
/usr/local/mysql/bin/mysqldump -uroot -p --single-transaction --master-data= -R --database wcd_ft > wcd_ft.sql
将备份文件传输到服务器2上。
登录数据库创建用于同步的数据库用户
mysql>grant replication slave on *.* to 'repl'@'%' identified by 'repl123';
mysql>flush privileges;
3、服务器2上导入数据库
在服务器2上导入刚备份的数据库
/usr/local/mysql_3307/bin/mysql -uroot -p -S /tmp/mysql_3307.sock < wcd_ft.sql
查看数据库备份文件 wcd_ft.sql 的主库master信息
grep -i "change master" wcd_ft.sql
结果：
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.007289', MASTER_LOG_POS=;
完成后，登录数据库配置主从库的同步。
CHANGE MASTER TO
MASTER_HOST='10.10.18.10（服务器1的ip地址）',
MASTER_USER='repl',
MASTER_PASSWORD='repl123',
MASTER_LOG_FILE='mysql-bin.007289',
MASTER_LOG_POS=;#启动slave从库
start slave；
查看从库的同步状态
show slave status\G;
配置完成。
