【CentOS 7MySQL主从配置1】,MySQL主从介绍和配置#180124

来源:转载


hellopasswd





MySQL主从介绍
MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做做主从后,在A上写数据,另一台B也会跟着写数据,两者数据实时同步
MySQL主从是基于binlog的,主上需要开启binlog才能进行主从。
主从过程大致有3歌步骤
1主将更改操作记录到binlog里
2从将主的binlog事件(sql语句)同步到本机上并记录在relaylog里
3从根据relaylog里面的sql语句按顺序执行
主上有一个log dump线程,用来和从的I/O线程传递binlog
从上有两个线程,其中I/O线程用来同步主的binlog并生成relaylog,另外一个SQL线程用来把relaylog里面的sql语句落地
主从配置-主上操作

安装mysql

修改my.cnf,增加server-id=130和log_bin=user1

修改完配置文件后,启动或重启mysqld服务

把mysql库备份并恢复成user库,作为测试数据

mysqldump -uroot -e "create database user"

mysql -uroot user < /tmp/mysql.sql

创建用作同步数据的用户

grant replication slave on . to 'repl'@slave_ip identified by 'password';

flush tables with read lock;

show master status;

Linux主机A

[[email protected] mysql]# ifconfig
eno16777736: flags=4163mtu 1500
inet 192.168.9.128netmask 255.255.255.0broadcast 192.168.9.255
inet6 fe80::20c:29ff:fe34:d226prefixlen 64scopeid 0x20
ether 00:0c:29:34:d2:26txqueuelen 1000(Ethernet)
RX packets 270460bytes 385242471 (367.3 MiB)
RX errors 0dropped 0overruns 0frame 0
TX packets 46386bytes 6350114 (6.0 MiB)
TX errors 0dropped 0 overruns 0carrier 0collisions 0
lo: flags=73mtu 65536
inet 127.0.0.1netmask 255.0.0.0
inet6 ::1prefixlen 128scopeid 0x10
looptxqueuelen 0(Local Loopback)
RX packets 90546bytes 45004980 (42.9 MiB)
RX errors 0dropped 0overruns 0frame 0
TX packets 90546bytes 45004980 (42.9 MiB)
TX errors 0dropped 0 overruns 0carrier 0collisions 0Linux主机B
[[email protected] mysql]# ifconfig
eno16777736: flags=4163mtu 1500
inet 192.168.9.132netmask 255.255.255.0broadcast 192.168.9.255
ether 00:0c:29:f2:c7:e9txqueuelen 1000(Ethernet)
RX packets 260694bytes 381203080 (363.5 MiB)
RX errors 0dropped 0overruns 0frame 0
TX packets 43187bytes 5495743 (5.2 MiB)
TX errors 0dropped 0 overruns 0carrier 0collisions 0
lo: flags=73mtu 65536
inet 127.0.0.1netmask 255.0.0.0
inet6 ::1prefixlen 128scopeid 0x10
looptxqueuelen 0(Local Loopback)
RX packets 0bytes 0 (0.0 B)
RX errors 0dropped 0overruns 0frame 0
TX packets 0bytes 0 (0.0 B)
TX errors 0dropped 0 overruns 0carrier 0collisions 0Linux主机A
[[email protected] ~]# vi /etc/my.cnf
添加
4 server-id=134
5 log_bin=user1


1 [mysqld]
2 datadir=/data/mysql
3 socket=/tmp/mysql.sock
4 server-id=134
5 log_bin=user1
6 # Disabling symbolic-links is recommended to prevent assorted security risks
7 symbolic-links=0
8 # Settings user and group are ignored when systemd is used.
9 # If you need to run mysqld under a different user or group,
10 # customize your systemd unit file for mariadb according to the
11 # instructions in http://fedoraproject.org/wiki/Systemd
12
13 [mysqld_safe]
14 #log-error=/var/log/mariadb/mariadb.log
15 #pid-file=/var/run/mariadb/mariadb.pid
16
17 #
18 # include all files from the config directory
19 #
20 #!includedir /etc/my.cnf.d
[[email protected] ~]# /etc/init.d/mysqld restart
Shutting down MySQL..... SUCCESS!
Starting MySQL............... SUCCESS!
[[email protected] ~]# cd /data/mysql/
[[email protected] mysql]# ls -lt /data/mysql/
total 110628
-rw-rw----. 1 mysql mysql 8056 Jan 21 09:25 localhost.localdomain.err
-rw-rw----. 1 mysql mysql 50331648 Jan 21 09:25 ib_logfile0
-rw-rw----. 1 mysql mysql 12582912 Jan 21 09:25 ibdata1
-rw-rw----. 1 mysql mysql 5 Jan 21 09:25 localhost.localdomain.pid
-rw-rw----. 1 mysql mysql15 Jan 21 09:25 user1.index
-rw-rw----. 1 mysql mysql120 Jan 21 09:25 user1.000001
drwx------. 2 mysql mysql 4096 Jan 21 08:09 zrlog
-rw-rw----. 1 mysql mysql56 Jan 21 07:52 auto.cnf
drwx------. 2 mysql mysql 4096 Jan 21 07:47 mysql
drwx------. 2 mysql mysql 4096 Jan 21 07:47 performance_schema
-rw-rw----. 1 mysql mysql 50331648 Jan 21 07:47 ib_logfile1
drwx------. 2 mysql mysql 6 Jan 21 07:47 test

生成索引user1.index和二进制日志文件user1.000001


备份数据


[[email protected] mysql]# mysqldump -uroot -p123 zrlog > /tmp/zrlog.sql
Warning: Using a password on the command line interface can be insecure.

创建库


[[email protected] mysql]# mysql -uroot -p123 -e "create database user"
Warning: Using a password on the command line interface can be insecure.

恢复数据


[[email protected] mysql]# mysql -uroot -p123user < /tmp/zrlog.sql
Warning: Using a password on the command line interface can be insecure.[[email protected] mysql]# ls -lt /data/mysql/
total 110640
-rw-rw----. 1 mysql mysql 50331648 Jan 21 10:04 ib_logfile0
-rw-rw----. 1 mysql mysql 12582912 Jan 21 10:04 ibdata1
-rw-rw----. 1 mysql mysql10258 Jan 21 10:04 user1.000001
drwx------. 2 mysql mysql 4096 Jan 21 10:04 user
-rw-rw----. 1 mysql mysql 8056 Jan 21 09:25 localhost.localdomain.err
-rw-rw----. 1 mysql mysql 5 Jan 21 09:25 localhost.localdomain.pid
-rw-rw----. 1 mysql mysql15 Jan 21 09:25 user1.index
drwx------. 2 mysql mysql 4096 Jan 21 08:09 zrlog
-rw-rw----. 1 mysql mysql56 Jan 21 07:52 auto.cnf
drwx------. 2 mysql mysql 4096 Jan 21 07:47 mysql
drwx------. 2 mysql mysql 4096 Jan 21 07:47 performance_schema
-rw-rw----. 1 mysql mysql 50331648 Jan 21 07:47 ib_logfile1
drwx------. 2 mysql mysql 6 Jan 21 07:47 test[[email protected] mysql]# mysql -uroot -p123
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.Commands end with ; or /g.
Your MySQL connection id is 1146
Server version: 5.6.35-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.
mysql> grant replication slave on *.* to 'repl'@'192.168.9.132' identified by '123a';
Query OK, 0 rows affected (0.25 sec)

锁定表,不再写入数据


mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| user1.000001 |10468 ||| |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> quit
Bye
[[email protected] mysql]# mysqldump -uroot -p123 mysql > /tmp/mysql.sql
Warning: Using a password on the command line interface can be insecure.
[[email protected] mysql]# mysqldump -uroot -p123 zrlog > /tmp/zrlog.sql
Warning: Using a password on the command line interface can be insecure.
从上操作

安装mysql

查看my.cnf,配置server-id=131,要求和主不一样

修改完配置文件后,启动或重启mysqld服务

把主上user库同步到从上

可以先创建user库,然后把主上的/tmp/mysql.sql拷贝到从上,然后导入user库

mysql -uroot

stop slave;

change master to master_home='',master_user='repl',master_password='',master_log_file='',master_log_pos=xx

start slave;

还要到主上执行unlock tables

Linux主机B

[[email protected] mysql]# ps aux | grep mysql
root99140.00.0 1132681628 pts/0S14:57 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/localhost.localdomain.pid
mysql 100490.0 24.4 1039352 458120 pts/0Sl 14:57 0:08 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/localhost.localdomain.err --pid-file=/data/mysql/localhost.localdomain.pid --socket=/tmp/mysql.sock
root194190.00.0 112656 976 pts/0S+ 18:06 0:00 grep --color=auto mysql[[email protected] mysql]# vi /etc/my.cnf
添加
4 server-id=132


[[email protected] mysql]# vi /etc/my.cnf
1 [mysqld]
2 datadir=/data/mysql
3 socket=/tmp/mysql.sock
4 server-id=132
5 # Disabling symbolic-links is recommended to prevent assorted security risks
6 symbolic-links=0
7 # Settings user and group are ignored when systemd is used.
8 # If you need to run mysqld under a different user or group,
9 # customize your systemd unit file for mariadb according to the
10 # instructions in http://fedoraproject.org/wiki/Systemd
11
12 [mysqld_safe]
13 #log-error=/var/log/mariadb/mariadb.log
14 #pid-file=/var/run/mariadb/mariadb.pid
15
16 #
17 # include all files from the config directory
18 #
19 #!includedir /etc/my.cnf.d
[[email protected] mysql]# /etc/init.d/mysqld restart
Shutting down MySQL... SUCCESS!
Starting MySQL................ SUCCESS! [[email protected] mysql]# scp 192.168.9.128:/tmp/*.sql /tmp/
The authenticity of host '192.168.9.128 (192.168.9.128)' can't be established.
ECDSA key fingerprint is 96:d6:ff:f1:49:7c:f2:99:02:71:c6:25:fd:93:29:be.
Are you sure you want to continue connecting (yes/no)? y
Please type 'yes' or 'no': yes
Warning: Permanently added '192.168.9.128' (ECDSA) to the list of known hosts.
[email protected]'s password:
mysql.sql100%648KB 648.0KB/s 00:00
zrlog.sql100% 9876 9.6KB/s 00:00
[[email protected] mysql]# mysqladmin -uroot password 123
Warning: Using a password on the command line interface can be insecure.
[[email protected] mysql]# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[[email protected] mysql]# mysql -uroot -p123
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.Commands end with ; or /g.
Your MySQL connection id is 7
Server version: 5.6.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.
mysql> create database user;
Query OK, 1 row affected (0.05 sec)
mysql> create database zrlog;
Query OK, 1 row affected (0.00 sec)
mysql> create database mysql1;
Query OK, 1 row affected (0.00 sec)
mysql> quit
Bye

修改于 180124

分享给朋友:
您可能感兴趣的文章:
随机阅读: