Mysql-5.5版本搭建實例的部分庫的從庫

由於業務需要在Mysql實例中創建部分庫的從庫，已有的Mysql實例的版本是mysql-5.5.49，是一個非常老的版本。

本文檔涉及到伺服器中運行多實例和構建實例中部分庫的從庫。

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;

配置完成。