一:mariadb主从复制
一、mariadb主
1.安装mariadb数据库
yum -y install mariadb mariadb-server mariadb-devel
2.配置主mariadb数据库
vim /etc/my.cnf
添加:
server-id=11
log-bin=master-bin
log-slave-updates=true
systemctl restart mariadb
mysqladmin -u root password 123.com
mysql -u root -p123.com
mysql> grant replication slave on *.* to 'slave'@'192.168.10.%' identified by '123.com';
mysql> flush privileges;
mysql> show master status;
二、mariadb从
1.安装mariadb数据库
yum -y install mariadb mariadb-server mariadb-devel
2.配置从mariadb数据库
vim /etc/my.cnf
添加:
server-id=21
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
注意:主—从的ID不能相同。
systemctl restart mariadb
mysqladmin -u root password 123.com
mysql -u root -p123.com
mysql> change master to master_host='192.168.10.1',master_user='slave',master_password='123.com',master_log_file='master-bin.000001',master_log_pos=107;
mysql> start slave;
mysql> show slave status\G //结尾不能加; 否则或报错。
Slave_IO_Running: Yes //确保为YES
Slave_SQL_Running: Yes //确保为YES
mysql> quit
再从服务器配置change的时候要改ip地址和节点 改成主服务器查出来的(show master status; )
问题说明:
1、如果出现ERROR: No query specified
则:原因是语法错误,去掉语句后的;号。
2、如果bin-log日志不更新,或者无法进行数据同步,
则:删除bin-log日志,重新启动服务即可。
三、测试
1)主数据库服务器:
[root@localhost ~]# mysql -u root -p123.com
mysql> show databases;
mysql> create database abc;
mysql> show databases;
mysql> use abc;
mysql> create table biao1 (id int,name char(20));
mysql> show tables;
2)从数据库服务器:
[root@localhost ~]# mysql -u root -p123.com
mysql> show databases;
mysql> use abc
mysql> show tables;
实验二:mysql-5.7主从复制
一、安装mysql(主从相同)
1、卸载系统默认安装的mysql(7.2以上系统默认安装的是mariadb)
[root@localhost ~]# rpm -qa |grep mariadb
[root@localhost ~]# rpm -e mariadb mariadb-server --nodeps
2、安装相关依赖关系
[root@localhost ~]# yum -y install ncurses-devel perl perl-devel perl-DBD*
[root@localhost ~]# tar -zxvf /media/cmake-28.6.tar.gz -C /usr/src
[root@localhost ~]# tar -zxvf /media/mysql-5.6.36.tar.gz -C /usr/src
[root@localhost Packages]# cd /usr/src/cmake-2.8.6/
[root@localhost cmake-2.8.6]# ./configure
[root@localhost cmake-2.8.6]# gmake
[root@localhost cmake-2.8.6]# gmake install
3、编译安装mysql
[root@localhost ~]# groupadd mysql
[root@localhost ~]# useradd -M -s /sbin/nologin -g mysql mysql
[root@localhost ~]# cd /usr/src/mysql-5.6.36
[root@localhost mysql-5.6.36]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all
[root@localhost mysql-5.6.36]# make
[root@localhost mysql-5.6.36]# make install
4、建立配置文件
[root@localhost /]# chown -R mysql:mysql /usr/local/mysql/
[root@localhost /]# rm -f /etc/my.cnf
[root@localhost /]# cp /usr/src/mysql-5.6.36/support-files/my-default.cnf /etc/my.cnf
5、初始化数据库
注意:需要安装perl组件可以使用yum安装
[root@localhost /]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --group=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
[root@localhost /]# vim /etc/profile
在最后添加:
PATH="$PATH:/usr/local/mysql/bin"
[root@localhost /]# ./etc/profile
[root@localhost /]# ln -s /usr/local/mysql/bin/* /usr/local/bin
6、添加服务
[root@localhost bin]# cp /usr/src/mysql-5.6.36/support-files/mysql.server /etc/init.d/mysqld
[root@localhost /]# chmod a+x /etc/init.d/mysqld
[root@localhost /]# chkconfig --add mysqld
[root@localhost /]# chkconfig mysqld on
启动服务:
[root@localhost /]# /etc/init.d/mysqld start
查看服务:
[root@localhost /]# ps -A |grep mysqld
[root@localhost /]# netstat -anpt |grep mysqld
关闭服务:
[root@localhost /]# /etc/init.d/mysqld stop
配置系统服务:
[root@localhost /]# vim /usr/lib/systemd/system/mysqld.service
添加:
[Unit]
Description=Mysql server
After=network.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE=5000
[Install]
WantedBy=multi-user.target
[root@localhost /]# systemctl restart mysqld.server
[root@localhost /]# systemctl enable mysqld.server
二、配置主mysql
[root@localhost ~]# vim /etc/my.cnf
添加:
server-id=11 //修改
log-bin=master-bin //修改
log-slave-updates=true //添加
systemctl restart mysqld.server
mysqladmin -u root password 123.com
mysql -u root -p123.com
mysql> grant replication slave on *.* to 'slave'@'192.168.10.%' identified by '123.com';
mysql> flush privileges;
mysql> show master status;
三、配置从mariadb数据库
vim /etc/my.cnf
添加:
server-id=21
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
注意:主—从的ID不能相同。
systemctl restart mysqld.server
mysqladmin -u root password 123.com
mysql -u root -p123.com
mysql> change master to master_host='192.168.10.1',master_user='slave',master_password='123.com',master_log_file='master-bin.000001',master_log_pos=107;
mysql> start slave;
mysql> show slave status\G
四、测试