fangbushi

目录

9个T
fangbushi

基础架构

第二代分布式解决方案
EBLE
mycat
第三代3-5年
NewSQL
fangbushi

环境准备

DB01
~~~shell
[root@db01 ~]# pkill mysqld
[root@db01 ~]# rm -rf /data/330*
[root@db01 ~]# mv /etc/my.cnf /etc/my.cnf.bak
mv: 无法获取"/etc/my.cnf" 的文件状态(stat): 没有那个文件或目录
[root@db01 ~]# mkdir /data/33{07..10}/data -p
[root@db01 ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/application/mysql
309/data --basedir=/application/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3310/data --basedir=/application/mysql
2019-07-01T03:45:32.418335Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-07-01T03:45:36.461855Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-07-01T03:45:37.190270Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-07-01T03:45:37.258379Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b01b7220-9bb2-11e9-9af4-000c29e3462d.
2019-07-01T03:45:37.263843Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-07-01T03:45:37.264721Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@db01 ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/application/mysql
2019-07-01T03:45:44.715830Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-07-01T03:45:54.323364Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-07-01T03:45:54.997332Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-07-01T03:45:55.061483Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: bab7fc9c-9bb2-11e9-9d00-000c29e3462d.
2019-07-01T03:45:55.062579Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-07-01T03:45:55.063485Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@db01 ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/application/mysql
2019-07-01T03:46:02.364833Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-07-01T03:46:11.179671Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-07-01T03:46:12.548460Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-07-01T03:46:12.612115Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: c52e0056-9bb2-11e9-9dda-000c29e3462d.
2019-07-01T03:46:12.656107Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-07-01T03:46:12.656854Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@db01 ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3310/data --basedir=/application/mysql
2019-07-01T03:46:24.160432Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-07-01T03:46:37.072335Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-07-01T03:46:41.300109Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-07-01T03:46:41.738541Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d68a5709-9bb2-11e9-a077-000c29e3462d.
2019-07-01T03:46:41.740760Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-07-01T03:46:41.743106Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@db01 ~]# clear
[root@db01 ~]# cat >/data/3307/my.cnf< [mysqld]

basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
-name-resolve
server-id=8
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat >/data/3309/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log-error=/data/3309/mysql.log
log_> port=3307

log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=7
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
[root@db01 ~]#
[root@db01 ~]# cat >/data/3308/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
binlog_format=row
skip-name-resolve
server-id=8
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
[root@db01 ~]#
[root@db01 ~]# cat >/data/3309/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log-error=/data/3309/mysql.log
log_bin=/data/3309/mysql-bin
binlog_format=row
skip-name-resolve
server-id=9
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
ion=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin> EOF
[root@db01 ~]# cat >/data/3310/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3310/data
socket=/data/3310/mysql.sock
port=3310
log-error=/data/3310/mysql.log
log_bin=/data/3310/mysql-bin
binlog_format=row
skip-name-resolve
server-id=10
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
[root@db01 ~]#
[root@db01 ~]# cat >/etc/systemd/system/mysqld3307.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF
[root@db01 ~]#
[root@db01 ~]# cat >/etc/systemd/system/mysqld3308.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF
[root@db01 ~]#
[root@db01 ~]# cat >/etc/systemd/system/mysqld3309.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF
[root@db01 ~]# cat >/etc/systemd/system/mysqld3310.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF
[root@db01 ~]# chown -R mysql.mysql /data/*
[root@db01 ~]# systemctl start mysqld3307
Warning: mysqld3307.service changed on disk. Run 'systemctl daemon-reload' to reload units.
[root@db01 ~]# systemctl start mysqld3308
Warning: mysqld3308.service changed on disk. Run 'systemctl daemon-reload' to reload units.
[root@db01 ~]# systemctl start mysqld3309
Warning: mysqld3309.service changed on disk. Run 'systemctl daemon-reload' to reload units.
[root@db01 ~]# systemctl start mysqld3310
[root@db01 ~]#
[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
ke 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 8 |
+---------------+-------+
[root@db01 ~]# mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
[root@db01 ~]# mysql -S /data/3310/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 10 |
+---------------+-------+
~~~
DB02

[root@db02 ~]# pkill mysqld
[root@db02 ~]# rm -rf /data/330* 
[root@db02 ~]# mv /etc/my.cnf /etc/my.cnf.bak
mv: 无法获取"/etc/my.cnf" 的文件状态(stat): 没有那个文件或目录
[root@db02 ~]# mkdir /data/33{07..10}/data -p
[root@db02 ~]# mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/application/mysql
2019-07-01T03:41:09.154771Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-07-01T03:41:15.508088Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-07-01T03:41:17.502521Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-07-01T03:41:17.714972Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 156844ab-9bb2-11e9-8ce3-000c296f06cd.
2019-07-01T03:41:17.774615Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-07-01T03:41:17.775619Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@db02 ~]# mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/application/mysql
2019-07-01T03:41:32.015671Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-07-01T03:41:47.054104Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-07-01T03:41:49.392474Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-07-01T03:41:49.653002Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 28719fd0-9bb2-11e9-8da1-000c296f06cd.
2019-07-01T03:41:49.655689Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-07-01T03:41:49.658690Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@db02 ~]# mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/app/mysql
2019-07-01T03:42:04.075739Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-07-01T03:42:04.075928Z 0 [ERROR] Can't find error-message file '/app/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2019-07-01T03:42:18.784249Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-07-01T03:42:23.071921Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-07-01T03:42:23.896544Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 3cdac61d-9bb2-11e9-8f6b-000c296f06cd.
2019-07-01T03:42:23.899887Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-07-01T03:42:23.903490Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@db02 ~]# mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/application/mysql
2019-07-01T03:43:01.711573Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-07-01T03:43:01.713903Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2019-07-01T03:43:01.714428Z 0 [ERROR] Aborting

[root@db02 ~]# mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/application/mysql
2019-07-01T03:43:52.625829Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-07-01T03:43:52.628338Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2019-07-01T03:43:52.629002Z 0 [ERROR] Aborting

[root@db02 ~]# clear
[root@db02 ~]# pkill mysqld
[root@db02 ~]# rm -rf /data/330* 
[root@db02 ~]# mv /etc/my.cnf /etc/my.cnf.bak
mv: 无法获取"/etc/my.cnf" 的文件状态(stat): 没有那个文件或目录
[root@db02 ~]# mkdir /data/33{07..10}/data -p
[root@db02 ~]# mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/application/mysql
309/data --basedir=/application/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3310/data --basedir=/application/mysql
2019-07-01T03:45:33.600565Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-07-01T03:45:44.647430Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-07-01T03:45:47.022048Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-07-01T03:45:47.086772Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b5f72495-9bb2-11e9-9583-000c296f06cd.
2019-07-01T03:45:47.092822Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-07-01T03:45:47.093703Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@db02 ~]# mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/application/mysql
2019-07-01T03:45:55.678424Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-07-01T03:45:59.480112Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-07-01T03:46:00.304041Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-07-01T03:46:00.367171Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: bde19217-9bb2-11e9-9733-000c296f06cd.
2019-07-01T03:46:00.369108Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-07-01T03:46:00.370548Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@db02 ~]# mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/application/mysql
2019-07-01T03:46:10.198484Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-07-01T03:46:19.185023Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-07-01T03:46:20.350789Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-07-01T03:46:20.641797Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: c9f73b99-9bb2-11e9-990a-000c296f06cd.
2019-07-01T03:46:20.660101Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-07-01T03:46:20.661390Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@db02 ~]# mysqld --initialize-insecure  --user=mysql --datadir=/data/3310/data --basedir=/application/mysql
2019-07-01T03:46:25.216201Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-07-01T03:46:41.003421Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-07-01T03:46:43.633003Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-07-01T03:46:43.957298Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d7dce55c-9bb2-11e9-9a66-000c296f06cd.
2019-07-01T03:46:43.961141Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-07-01T03:46:44.000844Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@db02 ~]# clear
[root@db02 ~]# cat >/data/3307/my.cnf<<EOF
> [mysqld]
> basedir=/application/mysql
> datadir=/data/3307/data
> socket=/data/3307/mysql.sock
> port=3307
> log-error=/data/3307/mysql.log
> log_bin=/data/3307/mysql-bin
> binlog_format=row
> skip-name-resolve
> server-id=17
> gtid-mode=on
> enforce-gtid-consistency=true
> log-slave-updates=1
> EOF
-name-resolve
server-id=18
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3309/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log-error=/data/3309/mysql.log
log_[root@db02 ~]# cat >/data/3308/my.cnf<<EOF
> [mysqld]
> basedir=/application/mysql
> datadir=/data/3308/data
> port=3308
> socket=/data/3308/mysql.sock
> log-error=/data/3308/mysql.log
> log_bin=/data/3308/mysql-bin
> binlog_format=row
> skip-name-resolve
> server-id=18
> gtid-mode=on
> enforce-gtid-consistency=true
> log-slave-updates=1
> EOF
[root@db02 ~]# cat >/data/3309/my.cnf<<EOF
> [mysqld]
> basedir=/application/mysql
> datadir=/data/3309/data
> socket=/data/3309/mysql.sock
> port=3309
> log-error=/data/3309/mysql.log
> log_bin=/data/3309/mysql-bin
> binlog_format=row
> skip-name-resolve
> server-id=19
> gtid-mode=on
> enforce-gtid-consistency=true
> log-slave-updates=1
> EOF
on=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/[root@db02 ~]# 
[root@db02 ~]# 
[root@db02 ~]# cat >/data/3310/my.cnf<<EOF
> [mysqld]
> basedir=/application/mysql
> datadir=/data/3310/data
> socket=/data/3310/mysql.sock
> port=3310
> log-error=/data/3310/mysql.log
> log_bin=/data/3310/mysql-bin
> binlog_format=row
> skip-name-resolve
> server-id=20
> gtid-mode=on
> enforce-gtid-consistency=true
> log-slave-updates=1
> EOF
[root@db02 ~]# 
[root@db02 ~]# cat >/etc/systemd/system/mysqld3307.service<<EOF
> [Unit]
> Description=MySQL Server
> Documentation=man:mysqld(8)
> Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
> After=network.target
> After=syslog.target
> [Install]
> WantedBy=multi-user.target
> [Service]
> User=mysql
> Group=mysql
> ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
> LimitNOFILE = 5000
> EOF
[root@db02 ~]# 
[root@db02 ~]# cat >/etc/systemd/system/mysqld3308.service<<EOF
> [Unit]
> Description=MySQL Server
> Documentation=man:mysqld(8)
> Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
> After=network.target
> After=syslog.target
> [Install]
> WantedBy=multi-user.target
> [Service]
> User=mysql
> Group=mysql
> ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
> LimitNOFILE = 5000
> EOF
[root@db02 ~]# 
[root@db02 ~]# cat >/etc/systemd/system/mysqld3309.service<<EOF
> [Unit]
> Description=MySQL Server
> Documentation=man:mysqld(8)
> Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
> After=network.target
> After=syslog.target
> [Install]
> WantedBy=multi-user.target
> [Service]
> User=mysql
> Group=mysql
> ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
> LimitNOFILE = 5000
> EOF
[root@db02 ~]# cat >/etc/systemd/system/mysqld3310.service<<EOF
> [Unit]
> Description=MySQL Server
> Documentation=man:mysqld(8)
> Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
> After=network.target
> After=syslog.target
> [Install]
> WantedBy=multi-user.target
> [Service]
> User=mysql
> Group=mysql
> ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
> LimitNOFILE = 5000
> EOF
[root@db02 ~]# chown -R mysql.mysql /data/*
[root@db02 ~]# systemctl start mysqld3307
Warning: mysqld3307.service changed on disk. Run 'systemctl daemon-reload' to reload units.
[root@db02 ~]# systemctl start mysqld3308
Warning: mysqld3308.service changed on disk. Run 'systemctl daemon-reload' to reload units.
[root@db02 ~]# systemctl start mysqld3309
Warning: mysqld3309.service changed on disk. Run 'systemctl daemon-reload' to reload units.
[root@db02 ~]# systemctl start mysqld3310
[root@db02 ~]# 
[root@db02 ~]# mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
ke 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 17    |
+---------------+-------+
[root@db02 ~]# mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 18    |
+---------------+-------+
[root@db02 ~]# mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 19    |
+---------------+-------+
[root@db02 ~]# mysql -S /data/3310/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 20    |
+---------------+-------+

搭建主从

主从规划

箭头指向谁是主库
    10.0.0.51:3307    <----->  10.0.0.52:3307
    10.0.0.51:3309    ------>  10.0.0.51:3307
    10.0.0.52:3309    ------>  10.0.0.52:3307

    10.0.0.52:3308  <----->    10.0.0.51:3308
    10.0.0.52:3310  ----->     10.0.0.52:3308
    10.0.0.51:3310  ----->     10.0.0.51:3308

分片规划

shard1:
    Master:10.0.0.51:3307
    slave1:10.0.0.51:3309
    Standby Master:10.0.0.52:3307
    slave2:10.0.0.52:3309
shard2:
    Master:10.0.0.52:3308
    slave1:10.0.0.52:3310
    Standby Master:10.0.0.51:3308
    slave2:10.0.0.51:3310

开始配置

shard1

10.0.0.51:3307 <-----> 10.0.0.52:3307
db02

mysql  -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"
    mysql  -S /data/3307/mysql.sock -e "grant all  on *.* to root@'10.0.0.%' identified by '123'  with grant option;"

db01

mysql  -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
    mysql  -S /data/3307/mysql.sock -e "start slave;"
    mysql  -S /data/3307/mysql.sock -e "show slave status\G"

db02

mysql  -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
    mysql  -S /data/3307/mysql.sock -e "start slave;"
    mysql  -S /data/3307/mysql.sock -e "show slave status\G"

10.0.0.51:3309 ------> 10.0.0.51:3307
db01

mysql  -S /data/3309/mysql.sock  -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
    mysql  -S /data/3309/mysql.sock  -e "start slave;"
    mysql  -S /data/3309/mysql.sock  -e "show slave status\G"

10.0.0.52:3309 ------> 10.0.0.52:3307
db02

mysql  -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
    mysql  -S /data/3309/mysql.sock -e "start slave;"
    mysql  -S /data/3309/mysql.sock -e "show slave status\G"

shard2

** 10.0.0.52:3308 <-----> 10.0.0.51:3308**
db01

mysql  -S /data/3308/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"
    mysql  -S /data/3308/mysql.sock -e "grant all  on *.* to root@'10.0.0.%' identified by '123'  with grant option;"

db02

mysql  -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
    mysql  -S /data/3308/mysql.sock -e "start slave;"
    mysql  -S /data/3308/mysql.sock -e "show slave status\G"

db01

mysql  -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
    mysql  -S /data/3308/mysql.sock -e "start slave;"
    mysql  -S /data/3308/mysql.sock -e "show slave status\G"

10.0.0.52:3310 -----> 10.0.0.52:3308
db02

mysql  -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
    mysql  -S /data/3310/mysql.sock -e "start slave;"
    mysql  -S /data/3310/mysql.sock -e "show slave status\G"

10.0.0.51:3310 -----> 10.0.0.51:3308
db01

mysql  -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
    mysql  -S /data/3310/mysql.sock -e "start slave;"
    mysql  -S /data/3310/mysql.sock -e "show slave status\G"

检测主从状态

    mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep Yes
    mysql -S /data/3308/mysql.sock -e "show slave status\G"|grep Yes
    mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep Yes
    mysql -S /data/3310/mysql.sock -e "show slave status\G"|grep Yes

MySQL分布式架构介绍

fangbushi

上一篇:vue 编译大量空格警告问题总结 warning: Replace `↹↹` with `··`


下一篇:Running setup.py install for pyicu ... error