数据库主从
一.准备环境
主数据库：172.16.1.51
从数据库：172.16.1.52
检查数据库服务是否打开
[root@db01 ~]# systemctl status mariadb.service
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Active: active (running) since Sat 2019-07-13 09:13:23 CST; 10min ago
Process: 2938 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
Process: 2906 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
Main PID: 2937 (mysqld_safe)
CGroup: /system.slice/mariadb.service
├─2937 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─3183 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql...
Jul 13 09:13:21 db01 systemd[1]: Starting MariaDB database server...
Jul 13 09:13:21 db01 mariadb-prepare-db-dir[2906]: Database MariaDB is probably initialized in /var/lib/...ne.
Jul 13 09:13:21 db01 mysqld_safe[2937]: 190713 09:13:21 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
Jul 13 09:13:22 db01 mysqld_safe[2937]: 190713 09:13:21 mysqld_safe Starting mysqld daemon with databas...ysql
Jul 13 09:13:23 db01 systemd[1]: Started MariaDB database server.
Hint: Some lines were ellipsized, use -l to show in full.
二.主数据库master修改
1.修改mysql配置文件
[root@db01 ~]# vim /etc/my.cnf
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysql]
default-character-set=utf8
[mysqld]
default-storage-engine=INNODB
character_set_server=utf8
log_bin=master-bin
log_bin_index=master-bin.index
binlog_do_db=test
server-id=1
"/etc/my.cnf" 30L, 770C
2.重启mysql，创建用于同步的用户账号
[root@db01 mariadb.service]# systemctl restart mariadb.service
[root@db01 mariadb.service]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select user,host,password from mysql.user;
+------+-----------+----------+
| user | host | password |
+------+-----------+----------+
| root | localhost | |
| root | db01 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | db01 | |
+------+-----------+----------+
6 rows in set (0.00 sec)
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'db02'@'172.16.1.52' IDENTIFIED BY 'oldboy123';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select user,host,password from mysql.user;
+------+-------------+-------------------------------------------+
| user | host | password |
+------+-------------+-------------------------------------------+
| root | localhost | |
| root | db01 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | db01 | |
| db02 | 172.16.1.52 | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 |
+------+-------------+-------------------------------------------+
7 rows in set (0.00 sec)
刷新权限.查看master状态，记录二进制文件名(mysql-bin.000001)和位置(245)
MariaDB [(none)]> flush privileges;
ery OK, 0 rows affected (0.00 sec)
▽
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000004 | 475 | test | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
3.操作完成将主数据库的所有数据移出库，在主库将数据库迁移到/tmp/下 (linux命令行执行)
[root@db01 mariadb.service]# mysqldump -uroot -poldboy123 --all-databases >/tmp/all.sql;
没有密码的使用下方命令导出
[root@db01 mariadb.service]# mysqldump --all-databases >/tmp/all.sql
[root@db01 mariadb.service]# ll /tmp
total 504
-rw-r--r-- 1 root root 515098 Jul 13 09:14 all.sql
发送到从库/tmp下
[root@db01 mariadb.service]# scp -rp /tmp/all.sql 172.16.1.52:/tmp/
The authenticity of host '172.16.1.52 (172.16.1.52)' can't be established.
ECDSA key fingerprint is SHA256:dZ7QkZoTFmlce+ed9PxciFC8VNiQBTT9i/6TkCaP7ZI.
ECDSA key fingerprint is MD5:ce:73:42:19:54:7e:ad:68:48:82:5c:ee:94:63:a4:d2.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.16.1.52' (ECDSA) to the list of known hosts.
root@172.16.1.52's password:
all.sql 100% 503KB 13.1MB/s 00:00
