由于业务需要在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
1 [client]
2 port = 3307
3 socket = /tmp/mysql_3307.sock
4
5 [mysqld]
6 port = 3307
7 socket = /tmp/mysql_3307.sock
8 datadir = /opt/mysql_3307
9 tmpdir=/tmp
10
11 skip-name-resolve
12 log_warnings = 2
13
14 #skip-grant-tables
15 big_tables = on
16 back_log = 600
17 max_connections = 3000
18 max_connect_errors = 3000
19 table_open_cache = 2048
20 max_allowed_packet = 128m
21 binlog_cache_size = 4m
22 max_heap_table_size = 1024m
23 read_buffer_size = 16m
24 read_rnd_buffer_size = 16m
25 sort_buffer_size = 8m
26 join_buffer_size = 16m
27 thread_cache_size = 600
28 thread_concurrency = 16
29 query_cache_size = 512m
30 query_cache_limit = 32m
31 default-storage-engine = myisam
32 #thread_stack = 192k
33 #transaction_isolation = repeatable read
34 tmp_table_size = 1024m
35
36 server-id = 10155
37 log-bin = mysql-bin
38 binlog_format = mixed
39 expire_logs_days = 8
40
41 replicate_wild_ignore_table = performance_schema.%
42 replicate_wild_ignore_table = information_schema.%
43 replicate_wild_ignore_table = mysql.%
44 replicate_wild_ignore_table = test.%
45 replicate_wild_ignore_table = tmp.%
46 #skip-name-resolve
47
48 slave_net_timeout = 30
49 #master-connect-retry = 10
50
51 log-slave-updates = 1
52
53 slow_query_log = sql-slow.log
54 long_query_time = 5
55 slave-skip-errors = 1062,1236
56
57 key_buffer_size = 2g
58 bulk_insert_buffer_size = 64m
59 myisam_sort_buffer_size = 128m
60 myisam_max_sort_file_size = 4g
61 myisam_repair_threads = 1
62 myisam_recover
63
64 innodb_additional_mem_pool_size = 16m
65 innodb_buffer_pool_size = 4g
66 innodb_data_file_path = ibdata1:256m:autoextend
67 innodb_write_io_threads = 8
68 innodb_read_io_threads = 8
69 innodb_thread_concurrency = 16
70 innodb_flush_log_at_trx_commit = 2
71 innodb_log_buffer_size = 16m
72 innodb_log_file_size = 256m
73 innodb_log_files_in_group = 3
74 innodb_max_dirty_pages_pct = 90
75 innodb_lock_wait_timeout = 60
76 innodb_file_per_table = 1
77
78 [mysqldump]
79 quick
80 max_allowed_packet = 32m
81
82 [mysql]
83 no-auto-rehash
84
85 [myisamchk]
86 key_buffer_size = 512m
87 sort_buffer_size = 512m
88 read_buffer = 8m
89 write_buffer = 8m
90
91 [mysqlhotcopy]
92 interactive-timeout
93
94 [mysqld_safe]
95 open-files-limit = 10240
96 #log-error=/opt/mysql_3307/mysqld.log
97 long_query_time = 3
98 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=2 -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=662625023;
完成后,登录数据库配置主从库的同步。
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=662625023;
#启动slave从库
start slave;
查看从库的同步状态
show slave status\g;
配置完成。
如您对本文有疑问或者有任何想说的,请点击进行留言回复,万千网友为您解惑!