mysql搭建实例_Mysql-5.5版本搭建实例的部分库的从库

由于业务需要在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;

配置完成。

如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！