mysql5.6+实操:主从同步 使用mysql_multi管理多实例

背景

小葫芦君最近在做一些技术总结,发现mysql5.6+以上的单机多实例,主从同步也挺有意思的,就顺手做了下笔记和实操,后续还会陆续的进行创业公司的技术体系,从cto的角度去探讨可行的方案,也从创始人的角色去思考一些问题和感悟。

具体实操

一、利用mysql_multi来管理多实例

mysql_multi的现实意义:
1:随着连接数上升,性能会下降,通过多实例来分流大量连接来提高性能。
2:做资源隔离
3:分库分表

mysql_multi是官方管理多实例的一个脚本,利用perl语言来编写的。

chown -R mysql.mysql /data/mysql
chmod +x /data/mysql

当所有配置文件弄好后,要进行数据库初始化
/usr/bin/mysql_install_db --user=mysql --datadir=/data/mysql/3306/data
/usr/bin/mysql_install_db --user=mysql --datadir=/data/mysql/3308/data

/usr/bin/mysqladmin -u root password 'xxxxxx' -S /data/mysql/3306/data/mysql.sock
mysql -uroot -p -S /data/mysql/3306/data/mysql.sock

/usr/bin/mysqladmin -u root password 'xxxxxx' -S /data/mysql/3308/data/mysql.sock
mysql -uroot -p -S /data/mysql/3308/data/mysql.sock

grant shutdown on . to 'admin'@'localhost' identified by 'admin';
grant shutdown on . to 'admin'@'127.0.0.1' identified by 'admin';
flush privileges;

mysql创建用户,并授予不同库的权限
grant all privileges on apidoc.* to apidoc@localhost identified by 'xxxxxx';
grant all privileges on apidoc.* to apidoc@"%" identified by 'xxxxxx';
flush privileges;

show global variables;

设置允许远程登录
mysql> use mysql;
mysql> select host,user,password from user;
mysql> update user set host='%' where user='root' and host='localhost';
mysql> flush privileges;
mysql> exit;

grant all privileges on . to 'root'@'localhost' identified by 'blissmall' with grant option;
grant all privileges on . to 'root'@'127.0.0.1' identified by 'blissmall' with grant option;
flush privileges;

关闭实例或者重启
分开不同端口进行关闭,启动也一样。
关闭需要带上密码。
mysqladmin -h127.0.0.1 -P3306 -uadmin -padmin shutdown
mysqld_multi stop 3306 --password=admin

两个实例一块关闭,一块启动。
mysqld_multi stop --password=admin
mysqld_multi start

mysql5.6这里有个bug,需要在mysqld_multi脚本里面做如下修改即可:
my $com= join ' ', 'my_print_defaults', @defaults_options, $group;
改成 --->
my $com= join ' ', 'my_print_defaults -s', @defaults_options, $group;

二、主从同步设置

微信图片_20170409201248.png
登陆3306主实例
1、建立一个复制用户:
mysql>grant replication slave, replication client on . to repl@'localhost' identified by 'repl';
grant replication slave, replication client on . to repl@'%' identified by 'repl';
mysql>flush privileges;
show grants for 'repl'@'%';

2、登陆3308从实例
mysql>change master to master_host='localhost', MASTER_PORT=3306, master_user='repl', master_password='repl', master_log_file='mysql-bin.000001', master_log_pos=0;

3、在主实例上执行
mysql>show master status;
SHOW PROCESSLIST;

4、在从实例上执行:
SHOW SLAVE STATUS;
SHOW PROCESSLIST;

5、修改密码
UPDATE user SET Password=PASSWORD('repl') where USER='repl';
FLUSH PRIVILEGES;

启动时发现有些表不存在,问题解决:
1、 删除上述系统表
drop table mysql.innodb_index_stats;
drop table mysql.innodb_table_stats;
drop table mysql.slave_master_info;
drop table mysql.slave_relay_log_info;
drop table mysql.slave_worker_info;

2、删除相关的.frm .ibd文件
rm -rf innodb_index_stats*
rm -rf innodb_table_stats*
rm -rf slave_master_info*
rm -rf slave_relay_log_info*
rm -rf slave_worker_info*

3、重新创建上述系统表
CREATE TABLE innodb_index_stats (

`database_name` VARCHAR (64) COLLATE utf8_bin NOT NULL,
`table_name` VARCHAR (64) COLLATE utf8_bin NOT NULL,
`index_name` VARCHAR (64) COLLATE utf8_bin NOT NULL,
`last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`stat_name` VARCHAR (64) COLLATE utf8_bin NOT NULL,
`stat_value` BIGINT (20) UNSIGNED NOT NULL,
`sample_size` BIGINT (20) UNSIGNED DEFAULT NULL,
`stat_description` VARCHAR (1024) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (
    `database_name`,
    `table_name`,
    `index_name`,
    `stat_name`
)

) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_bin STATS_PERSISTENT = 0;

CREATE TABLE innodb_table_stats (

`database_name` VARCHAR (64) COLLATE utf8_bin NOT NULL,
`table_name` VARCHAR (64) COLLATE utf8_bin NOT NULL,
`last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`n_rows` BIGINT (20) UNSIGNED NOT NULL,
`clustered_index_size` BIGINT (20) UNSIGNED NOT NULL,
`sum_of_other_index_sizes` BIGINT (20) UNSIGNED NOT NULL,
PRIMARY KEY (
    `database_name`,
    `table_name`
)

) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_bin STATS_PERSISTENT = 0;

CREATE TABLE slave_master_info (

`Number_of_lines` INT (10) UNSIGNED NOT NULL COMMENT 'Number of lines in the file.',
`Master_log_name` text CHARACTER

SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
Master_log_pos BIGINT (20) UNSIGNED NOT NULL COMMENT 'The master log position of the last read event.',
Host CHAR (64) CHARACTER
SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
User_name text CHARACTER
SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
User_password text CHARACTER
SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
Port INT (10) UNSIGNED NOT NULL COMMENT 'The network port used to connect to the master.',
Connect_retry INT (10) UNSIGNED NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
Enabled_ssl TINYINT (1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
Ssl_ca text CHARACTER
SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
Ssl_capath text CHARACTER
SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
Ssl_cert text CHARACTER
SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
Ssl_cipher text CHARACTER
SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
Ssl_key text CHARACTER
SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
Ssl_verify_server_cert TINYINT (1) NOT NULL COMMENT 'Whether to verify the server certificate.',
Heartbeat FLOAT NOT NULL,
Bind text CHARACTER
SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
Ignored_server_ids text CHARACTER
SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
Uuid text CHARACTER
SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
Retry_count BIGINT (20) UNSIGNED NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
Ssl_crl text CHARACTER
SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
Ssl_crlpath text CHARACTER
SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
Enabled_auto_position TINYINT (1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
PRIMARY KEY (Host, Port)
) ENGINE = INNODB DEFAULT CHARSET = utf8 STATS_PERSISTENT = 0 COMMENT = 'Master Information';

CREATE TABLE slave_relay_log_info (

`Number_of_lines` INT (10) UNSIGNED NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
`Relay_log_name` text CHARACTER

SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
Relay_log_pos BIGINT (20) UNSIGNED NOT NULL COMMENT 'The relay log position of the last executed event.',
Master_log_name text CHARACTER
SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
Master_log_pos BIGINT (20) UNSIGNED NOT NULL COMMENT 'The master log position of the last executed event.',
Sql_delay INT (11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
Number_of_workers INT (10) UNSIGNED NOT NULL,
Id INT (10) UNSIGNED NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
PRIMARY KEY (Id)
) ENGINE = INNODB DEFAULT CHARSET = utf8 STATS_PERSISTENT = 0 COMMENT = 'Relay Log Information';

CREATE TABLE slave_worker_info (

`Id` INT (10) UNSIGNED NOT NULL,
`Relay_log_name` text CHARACTER

SET utf8 COLLATE utf8_bin NOT NULL,
Relay_log_pos BIGINT (20) UNSIGNED NOT NULL,
Master_log_name text CHARACTER
SET utf8 COLLATE utf8_bin NOT NULL,
Master_log_pos BIGINT (20) UNSIGNED NOT NULL,
Checkpoint_relay_log_name text CHARACTER
SET utf8 COLLATE utf8_bin NOT NULL,
Checkpoint_relay_log_pos BIGINT (20) UNSIGNED NOT NULL,
Checkpoint_master_log_name text CHARACTER
SET utf8 COLLATE utf8_bin NOT NULL,
Checkpoint_master_log_pos BIGINT (20) UNSIGNED NOT NULL,
Checkpoint_seqno INT (10) UNSIGNED NOT NULL,
Checkpoint_group_size INT (10) UNSIGNED NOT NULL,
Checkpoint_group_bitmap BLOB NOT NULL,
PRIMARY KEY (Id)
) ENGINE = INNODB DEFAULT CHARSET = utf8 STATS_PERSISTENT = 0 COMMENT = 'Worker Information';

mysqld_multi stop --password=admin
mysqld_multi start

然后完成多实例和主从同步设置。

后续会增加主主同步的配置文章

后续可以做成自动化的一键脚本进行完成自动扩容。

添加新评论