本文主要是介绍MySQL5.7安装和主从复制，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

 

服务器主机名       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

安装MySQL

yum 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-node1

master

cat > /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

slave

cat > /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-node2

master

cat > /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

slave

cat > /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-node1

master

cd /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)

slave

cd /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-node2

master

cd /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)

slave

cd /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端更新数据，其他任意端都可以更新数据
• 两台服务器配置了间隔自增长，数据不同冲突

 

 

这篇关于MySQL5.7安装和主从复制的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！