mysql主从复制

mysql 主服务器配置

yum install mariadb-server -y
vim /etc/my.cnf
[mysqld]
server_id=7
log_bin=/data/logbin/mariadb-bin

mkdir /data/logbin
chown -R mysql.mysql /data/logbin
systemctl start mariadb
mysql < hellodb_innodb.sql
mysql
MariaDB [(none)]> grant replication slave on *.* to 'replicationuser'@'192.168.43.%' identified by 'centos';
mysqldump -A -F --single-transaction --master-data=1 -q > /data/all.sql
scp /data/all.sql 192.168.43.17:/data/

mysql 从服务器配置

yum install mariadb-server -y
vim /etc/my.cnf
[mysqld]
server_id=17

systemctl start mariadb

vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='192.168.43.7',
MASTER_USER='replicationuser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000005', MASTER_LOG_POS=245;

mysql < /data/all.sql
mysql
MariaDB [(none)]> show slave status\G
MariaDB [(none)]> start slave;

不小心在从服务器上作了配置

MariaDB [(none)]> create database db2; //在主服器上及时发现了，也在主服务器上加了这条配置

-- 在从服务器上发现了如下错误
 Last_Errno: 1007
                   Last_Error: Error 'Can't create database 'db2'; database exists' on query. Default database: 'db2'. Query: 'create database db2'

MariaDB [(none)]> drop database db2;
MariaDB [(none)]> stop slave;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G //结果发现同步了

--如果找不到解决方法，可以这样解决
MariaDB [(none)]> set global sql_slave_skip_counter=1;
MariaDB [(none)]> stop slave;
MariaDB [(none)]> start slave;

-- 如下错误 
 Last_Errno: 1062
                   Last_Error: Error 'Duplicate entry '5' for key 'PRIMARY'' on query. Default database: 'hellodb'. Query: 'insert into teachers(name,age)values('lisi',26)'

vim /etc/my.cnf
[mysqld]
slave_skip_errors=1062

systemctl restart mariadb

添加一台从服务器

[master]# mysqldump -A -F --single-transaction --master-data=1 -q > /data/all2.sql
[master]# scp /data/all2.sql 192.168.43.27:/data/
[slave2]# yum install mariadb-server -y
[slave2]# vim /etc/my.cnf
[mysqld]
server_id=27
[slave2]# systemctl start mariadb
[slave2]# vim /data/all2.sql
CHANGE MASTER TO 
MASTER_HOST='192.168.43.7',
MASTER_USER='replicationuser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000006', MASTER_LOG_POS=245;

[slave2]# mysql < /data/all2.sql
[slave2]# mysql
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G 

主服务器发生意外宕机了，提升一台从为主服务器

[slave1] MariaDB [(none)]> stop slave;
[slave1] MariaDB [(none)]> reset slave all;
[slave1]# vim /etc/my.cnf
log_bin=/data/logbin/mariadb-bin

[slave1]# chown -R mysql.mysql /data/logbin
[slave1]# systemctl restart mariadb
[slave1]# MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 |      245 |              |                  |
+--------------------+----------+--------------+------------------+

[slave2]# MariaDB [(none)]> stop slave;
[slave2]# MariaDB [(none)]> reset slave all;
[slave2]# MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.43.17',
    -> MASTER_USER='replicationuser',
    -> MASTER_PASSWORD='centos',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mariadb-bin.000001',
    -> MASTER_LOG_POS=245;
[slave2]# MariaDB [(none)]> start slave;