MySQL5.7安装和主从复制

2023-01-04 08:51:33
 
服务器主机名       ip                  说明
 
Mysql-node1      192.168.56.12      物理数据库1
 
Mysql-node2      192.168.56.13      物理数据库2
创建mysql用户groupadd mysql
useradd -r -g mysql -s /bin/false mysql
安装MySQLyum install -y libaio
cd /usr/local/src/
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
tar -zxf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
cp -rf mysql-5.7.17-linux-glibc2.5-x86_64 /data/app/mysql-3306
cp -rf mysql-5.7.17-linux-glibc2.5-x86_64 /data/app/mysql-3307
chown -R mysql:mysql /data/app/mysql-3306
chown -R mysql:mysql /data/app/mysql-3307
/data/app/mysql-3306/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql-3306 --datadir=/data/app/mysql-3306/data
/data/app/mysql-3307/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql-3307 --datadir=/data/app/mysql-3307/data
修改my.cnf需要修改的参数： 
server-id：保证每个配置文件唯一
两台master的自增长ID必须不同
linux-node1mastercat > /data/app/mysql-3306/my.cnf<<EOF
[client]
port = 3306
socket = /data/app/mysql-3306/mysql.sock
[mysqld]
 
port = 3306
user = mysql
server-id = 1
bind-address = 0.0.0.0
basedir = /data/app/mysql-3306
datadir = /data/app/mysql-3306/data
socket = /data/app/mysql-3306/mysql.sock
pid-file = /data/app/mysql-3306/mysql.pid
log-error = /data/app/mysql-3306/mysqld.log
 
skip-name-resolve
log_bin = mysql-bin
log-slave-updates
auto-increment-increment = 2
auto-increment-offset = 1
 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
EOF
chown mysql.mysql /data/app/mysql-3306/my.cnf
slavecat > /data/app/mysql-3307/my.cnf<<EOF
[client]
port = 3307
socket = /data/app/mysql-3307/mysql.sock
[mysqld]
 
port = 3307
user = mysql
server-id = 11
bind-address = 0.0.0.0
basedir = /data/app/mysql-3307
datadir = /data/app/mysql-3307/data
socket = /data/app/mysql-3307/mysql.sock
pid-file = /data/app/mysql-3307/mysql.pid
log-error = /data/app/mysql-3307/mysqld.log
 
skip-name-resolve
log_bin = mysql-bin
 
 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
EOF
chown mysql.mysql /data/app/mysql-3307/my.cnf
linux-node2mastercat > /data/app/mysql-3306/my.cnf<<EOF
[client]
port = 3306
socket = /data/app/mysql-3306/mysql.sock
[mysqld]
 
port = 3306
user = mysql
server-id = 2
bind-address = 0.0.0.0
basedir = /data/app/mysql-3306
datadir = /data/app/mysql-3306/data
socket = /data/app/mysql-3306/mysql.sock
pid-file = /data/app/mysql-3306/mysql.pid
log-error = /data/app/mysql-3306/mysqld.log
 
skip-name-resolve
log_bin = mysql-bin
log-slave-updates
auto-increment-increment = 2
auto-increment-offset = 2
 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
EOF
chown mysql.mysql /data/app/mysql-3306/my.cnf
slavecat > /data/app/mysql-3307/my.cnf<<EOF
[client]
port = 3307
socket = /data/app/mysql-3307/mysql.sock
[mysqld]
 
port = 3307
user = mysql
server-id = 22
bind-address = 0.0.0.0
basedir = /data/app/mysql-3307
datadir = /data/app/mysql-3307/data
socket = /data/app/mysql-3307/mysql.sock
pid-file = /data/app/mysql-3307/mysql.pid
log-error = /data/app/mysql-3307/mysqld.log
 
skip-name-resolve
log_bin = mysql-bin
 
 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
EOF
chown mysql.mysql /data/app/mysql-3307/my.cnf
启动MySQL启动服务linux-node1和linux-node2都执行如下命令
touch /data/app/mysql-3306/mysqld.log && chown mysql.mysql /data/app/mysql-3306/mysqld.log
sed -i 's#/usr/local/mysql#/data/app/mysql-3306#g' /data/app/mysql-3306/bin/mysqld_safe
/data/app/mysql-3306/bin/mysqld_safe --defaults-file=/data/app/mysql-3306/my.cnf --basedir=/data/app/mysql-3306 --datadir=/data/app/mysql-3306/data --user=mysql &
 
 
touch /data/app/mysql-3307/mysqld.log && chown mysql.mysql /data/app/mysql-3307/mysqld.log
sed -i 's#/usr/local/mysql#/data/app/mysql-3307#g' /data/app/mysql-3307/bin/mysqld_safe
/data/app/mysql-3307/bin/mysqld_safe --defaults-file=/data/app/mysql-3307/my.cnf --basedir=/data/app/mysql-3307 --datadir=/data/app/mysql-3307/data --user=mysql &
检查端口ss -lntup |egrep '3306|3307'
tcp    LISTEN     0      80                     *:3306                  *:*      users:(("mysqld",19973,22))
tcp    LISTEN     0      80                     *:3307                  *:*      users:(("mysqld",20537,22))
设置开机自启cp   support-files/mysql.server /etc/rc.d/init.d/mysqld
赋予可执行权限：chmod +x /etc/init.d/mysqld
添加为服务: chkconfig --add mysqld
查看服务列表:  chkconfig --list
看到3、4、5状态为开或者为 on 则表示成功。如果是 关或者 off 则执行一下：chkconfig --level 345 mysqld on
重启计算机:reboot
配置双主配置主从linux-node1mastercd /data/app/mysql-3306/
 
./bin/mysql -uroot -p -S mysql.sock -P 3306
mysql> CREATE USER 'repl'@'192.%' IDENTIFIED BY 'mysql';
Query OK, 0 rows affected (0.05 sec)
 
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.%';
Query OK, 0 rows affected (0.00 sec)
 
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      613 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
slavecd /data/app/mysql-3307/
./bin/mysql -uroot -p -S mysql.sock -P 3307
mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.12',
    ->                  MASTER_PORT=3306,
    ->                  MASTER_USER='repl',
    ->                  MASTER_PASSWORD='mysql',
    ->                  MASTER_LOG_FILE='mysql-bin.000001',
    ->                  MASTER_LOG_POS=613;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.12
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 613
               Relay_Log_File: linux-node1-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
linux-node2mastercd /data/app/mysql-3306/
./bin/mysql -uroot -p -S mysql.sock -P 3306
mysql> CREATE USER 'repl'@'192.%' IDENTIFIED BY 'mysql';
Query OK, 0 rows affected (0.05 sec)
 
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.%';
Query OK, 0 rows affected (0.00 sec)
 
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      613 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
slavecd /data/app/mysql-3307/
./bin/mysql -uroot -p -S mysql.sock -P 3307
mysql>
CHANGE MASTER TO MASTER_HOST='192.168.56.13',
                 MASTER_PORT=3306,
                 MASTER_USER='repl',
                 MASTER_PASSWORD='mysql',
                 MASTER_LOG_FILE='mysql-bin.000001',
                 MASTER_LOG_POS=613;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.13
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 613
               Relay_Log_File: linux-node1-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
配置双主master的binlog位置
linux-node1 master端
d /data/app/mysql-3306/
./bin/mysql -uroot -p -S mysql.sock -P 3306
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      613 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

linux-node2 master端
cd /data/app/mysql-3306/
./bin/mysql -uroot -p -S mysql.sock -P 3306
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      613 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

linux-node1 master配置跟linux-node2 master同步
mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.13',
                 MASTER_PORT=3306,
                 MASTER_USER='repl',
                 MASTER_PASSWORD='mysql',
                 MASTER_LOG_FILE='mysql-bin.000001',
                 MASTER_LOG_POS=613;
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
 
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.13
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 613
               Relay_Log_File: linux-node1-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes        

linux-node2 master配置跟linux-node1 master同步
mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.12',
                 MASTER_PORT=3306,
                 MASTER_USER='repl',
                 MASTER_PASSWORD='mysql',
                 MASTER_LOG_FILE='mysql-bin.000001',
                 MASTER_LOG_POS=613;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
 
mysql>
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.12
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 613
               Relay_Log_File: linux-node2-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
验证
从linux-node1上验证

在linux-node1 master上创建数据
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
 
mysql> use test;
Database changed
 
mysql> create table temp(id int,name varchar(64));
Query OK, 0 rows affected (0.11 sec)
 
mysql> insert into temp values(1,'aaa');
Query OK, 1 row affected (0.28 sec)
 
mysql> CREATE TABLE temp2(id INT ,nname VARCHAR(64));
Query OK, 0 rows affected (0.01 sec)
 
mysql> insert into temp2(nname) values('bbb');
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from test.temp;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
+------+------+
1 row in set (0.01 sec)

在linux-node1 slave上查看数据
mysql> select * from test.temp;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
+------+------+
1 row in set (0.00 sec)

在linux-node2 master上查看数据
mysql> select * from test.temp;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
+------+------+
1 row in set (0.00 sec)

在linux-node2 slave上查看数据
mysql> select * from test.temp;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
+------+------+
1 row in set (0.00 sec)

从linux-node2上验证

在linux-node2 master上创建数据
mysql> use test;
 
mysql> insert into temp2(nname) values('ddd');
Query OK, 1 row affected (0.02 sec)
mysql>  insert into temp2(nname) values('fff');
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from test.temp2;
+----+-------+
| id | nname |
+----+-------+
|  1 | bbb   |
|  2 | ddd   |
|  4 | fff   |
+----+-------+

在linux-node2 slave上查看数据
mysql> select * from test.temp2;
+----+-------+
| id | nname |
+----+-------+
|  1 | bbb   |
|  2 | ddd   |
|  4 | fff   |
+----+-------+
3 rows in set (0.00 sec)

在linux-node1 master上查看数据
mysql> select * from test.temp2;
+----+-------+
| id | nname |
+----+-------+
|  1 | bbb   |
|  2 | ddd   |
|  4 | fff   |
+----+-------+
3 rows in set (0.00 sec)

在linux-node1 slave上查看数据
mysql> select * from test.temp2;
+----+-------+
| id | nname |
+----+-------+
|  1 | bbb   |
|  2 | ddd   |
|  4 | fff   |
+----+-------+
3 rows in set (0.00 sec)
结论在任意一个master端更新数据，其他任意端都可以更新数据
两台服务器配置了间隔自增长，数据不同冲突
 
 
 
码农公寓

创建mysql用户

安装MySQL

修改my.cnf

linux-node1

master

slave

linux-node2

master

slave

启动MySQL

启动服务

检查端口

设置开机自启

配置双主

配置主从

linux-node1

master

slave

linux-node2

master

slave

配置双主

master的binlog位置

linux-node1 master端

linux-node2 master端

linux-node1 master配置跟linux-node2 master同步

linux-node2 master配置跟linux-node1 master同步

验证

从linux-node1上验证

在linux-node1 master上创建数据

在linux-node1 slave上查看数据

在linux-node2 master上查看数据

在linux-node2 slave上查看数据

从linux-node2上验证

在linux-node2 master上创建数据

在linux-node2 slave上查看数据

在linux-node1 master上查看数据

在linux-node1 slave上查看数据

结论

相关文章
