花了小一天的时间,终于实现了centos7 mariadb主从复制配置搭建,下面记录一下过程
环境:
虚拟机:vm8; centos7 版本:7.2.1511; mariadb 版本:centos7.2内置的
主库服务器: 10.69.5.200,CentOS 7,MariaDB 10已安装,有数据。
从库服务器1: 10.69.5.201,CentOS 7,MariaDB 10已安装,无应用数据。
主服务器配置
以下操作在主服务器192.168.71.151的/etc/my.cnf上进行。
1.修改配置文件,命令:vim /etc/my.cnf,输入下列代码:
[root@localhost ~]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock `# 新添加的部分 # 配置主从时需要添加以下信息 start innodb_file_per_table=NO log-bin=/var/lib/mysql/master-bin #log-bin没指定存储目录,则是默认datadir指向的目录 binlog_format=mixed server-id=200 #每个服务器都需要添加server_id配置,各个服务器的server_id需要保证唯一性,实践中通常设置为服务器IP地址的最后一位 #配置主从时需要添加以下信息 end ` # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # 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
最后,:wq!保存退出
2.重启mariadb服务,输入命令
[root@localhost ~]# systemctl restart mariadb.service
3.登录mariadb
[root@localhost ~]# mysql -u root -padmin
注:-p后是密码,中间没有空格
4.创建帐号并赋予replication的权限
从库,从主库复制数据时需要使用这个帐号进行
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'root'@'10.69.5.%' IDENTIFIED BY 'admin'; Query OK, 0 rows affected (0.00 sec)
5.备份数据库数据,用于导入到从数据库中
加锁
实际工作中,备份的时候是不让往库中写数据的,所以数据库要加锁,只能读
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.00 sec)
记录主库log文件及其当前位置
MariaDB [(none)]> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 694 | | | +------------------+----------+--------------+------------------+
记住File和Position的部分,从服务器会用到
备份数据,输入命令:
[root@localhost ~]# mysqldump -uroot -p --all-databases > /root/db.sql
解锁 主库
数据备份完成后,就可以释放主库上的锁:
MariaDB [(none)]> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)
从服务器配置
以下在从服务器上的操作
1.导入主库的数据
[root@localhost ~]# mysql -uroot -p < db.sql
2.从服务器/etc/my.cnf配置,设置relay-log
my.cnf文件中添加一行relay_log=relay-bin
如果不设置,默认是按主机名 + “-relay-bin”生成relay log。
[root@localhost ~]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 `#配置主从时需要添加以下信息 start innodb_file_per_table=NO server-id=201 #一般与服务器ip的最后数字一致 relay-log=/var/lib/mysql/relay-bin #配置主从时需要添加以下信息 end ` # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # 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
3.重启服务
[root@localhost ~]# systemctl restart mariadb.service
4.登录mariadb
[root@localhost ~]# mysql -u root -padmin
5.设置主从复制
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.69.5.200',MASTER_USER='root', MASTER_PASSWORD='admin', MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS= 694; Query OK, 0 rows affected (0.02 sec)
这个命令完成以下几个任务:
a.设置当前服务器为主服务器(10.69.5.200)的从库
b.提供当前数据库(从库)从主库复制数据时所需的用户名和密码,即上面的GRANT REPLICATION SLAVE ON *.* TO 'root'@'10.69.5.%' IDENTIFIED BY 'admin';设置的
c.指定从库开始复制主库时需要使用的日志文件和文件位置,即上面主库执行SHOW MASTER STATUS;显示结果中的File和Position
6.开启主从复制
MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.00 sec)
7.查看从库状态
MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.69.5.200 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 694 Relay_Log_File: relay-bin.000003 Relay_Log_Pos: 530 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 694 Relay_Log_Space: 818 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 200 1 row in set (0.00 sec)
注意:结果中Slave_IO_Running和Slave_SQL_Running必须为Yes,如果不是,需要根据提示的错误修改。
验证
主服务器:
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | mytest | | performance_schema | | test | +--------------------+ 5 rows in set (0.04 sec) MariaDB [(none)]> use mytest; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [mytest]> select * from user; +----+------+ | id | name | +----+------+ | 1 | t | | 2 | t2 | | 3 | t3 | +----+------+ 3 rows in set (0.00 sec) MariaDB [mytest]> insert into user(name) values('t4'); Query OK, 1 row affected (0.01 sec) MariaDB [mytest]> select * from user; +----+------+ | id | name | +----+------+ | 1 | t | | 2 | t2 | | 3 | t3 | | 4 | t4 | +----+------+ 4 rows in set (0.00 sec)
查看从服务器数据是否变化:
MariaDB [(none)]> use mytest; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [mytest]> select * from user; +----+------+ | id | name | +----+------+ | 1 | t | | 2 | t2 | +----+------+ 2 rows in set (0.00 sec) MariaDB [mytest]> select * from user; +----+------+ | id | name | +----+------+ | 1 | t | | 2 | t2 | | 4 | t4 | +----+------+ 3 rows in set (0.00 sec)
可以看到,从服务器更新了数据
搭建过程中遇到的问题及解决方法
问题1:从服务器设置主从复制出现错误:
MariaDB [mytest]> start slave; ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MariaDB error log
发现
Slave_IO_Running: No Slave_SQL_Running: No
进一步发现我输入的是:CHANGE MASTER TO MASTER_HOST='192.168.71.151',MASTER_USER='slave_user', MASTER_PASSWORD='bigs3cret', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 469;
重新输入:MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.69.5.200',MASTER_USER='root', MASTER_PASSWORD='admin', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 469;
报错:ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MariaDB error log
于是看错误日志:/var/log/mariadb/mariadb.log
错误日志的位置在/etc/my.cnf中配置:log-error=/
[root@localhost ~]# cat /var/log/mariadb/mariadb.log 160915 12:52:02 [ERROR] Failed to open the relay log './mariadb-relay-bin.000001' (relay_log_pos 4) 160915 12:52:02 [ERROR] Could not find target log during relay log initialization
通过查找答案: 删除/var/lib/mysql/路径下the ‘master.info' ‘mysqld-relay-bin.*' ‘relay-log.info' ‘relay-log-index.*'
运行命令:rm -rf master.info,rm -rf *relay*
重启服务:[root@localhost mysql]# systemctl restart mariadb.service
进入mariadb:
[root@localhost mysql]# mysql -u root -padmin MariaDB [(none)]> flush logs; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> reset slave; Query OK, 0 rows affected (0.00 sec)
重新设置主从复制关系:
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.69.5.200',MASTER_USER='root', MASTER_PASSWORD='admin', MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS= 694; Query OK, 0 rows affected (0.02 sec)
这次成功了。
MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.01 sec)
查看从库状态:
MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 10.69.5.200 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 694 Relay_Log_File: relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Connecting Slave_SQL_Running: Yes ··· ··· ··· Replicate_Ignore_Server_Ids: Master_Server_Id: 0 1 row in set (0.00 sec)
发现问题2.Slave_IO_Running: Connecting
问题2.Slave_IO_Running: Connecting
查看错误日志
[root@localhost ~]# cat /var/log/mariadb/mariadb.log ··· 160915 13:17:56 [Note] Slave SQL thread initialized, starting replication in log 'master-bin.000001' at position 694, relay log '/var/lib/mysql/relay-bin.000001' position: 4 160915 13:17:56 [ERROR] Slave I/O: error connecting to master 'root@10.69.5.200:3306' - retry-time: 60 retries: 86400 message: Can't connect to MySQL server on '10.69.5.200' (113), Error_code: 2003
这时运行telnet命令
[root@localhost ~]# telnet 10.69.5.200 3306
-bash: telnet: 未找到命令
安装telnet
[root@localhost ~]# yum -y install telnet-server.x86_64
安装成功后重启telnet服务
[root@localhost ~]# systemctl start telnet.socket [root@localhost ~]# systemctl enable telnet.socket [root@localhost ~]# telnet 10.69.5.200 3306
-bash: telnet: 未找到命令
还是不行
这回我reboot重启虚拟机,运行命令
注意:这回不是"yum -y install telnet-server.x86_64"了,这回没有telnet-server了
[root@localhost ~]# yum install telnet.x86_64
运行成功了
接着
[root@localhost ~]# systemctl enable telnet.socket [root@localhost ~]# systemctl start telnet.socket [root@localhost ~]# firewall-cmd --add-service=telnet --permanent success [root@localhost ~]# telnet telnet>
telnet终于安装成功了
从最新版本的centos7系统开始,默认的是 Mariadb而不是mysql!
使用系统自带的repos安装很简单:
yum install mariadb mariadb-server
- systemctl start mariadb ==> 启动mariadb
- systemctl enable mariadb ==> 开机自启动
- mysql_secure_installation ==> 设置 root密码等相关
- mysql -u root -p 123456 ==> 测试登录!
结束!
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
稳了!魔兽国服回归的3条重磅消息!官宣时间再确认!
昨天有一位朋友在大神群里分享,自己亚服账号被封号之后居然弹出了国服的封号信息对话框。
这里面让他访问的是一个国服的战网网址,com.cn和后面的zh都非常明白地表明这就是国服战网。
而他在复制这个网址并且进行登录之后,确实是网易的网址,也就是我们熟悉的停服之后国服发布的暴雪游戏产品运营到期开放退款的说明。这是一件比较奇怪的事情,因为以前都没有出现这样的情况,现在突然提示跳转到国服战网的网址,是不是说明了简体中文客户端已经开始进行更新了呢?
更新日志
- 小骆驼-《草原狼2(蓝光CD)》[原抓WAV+CUE]
- 群星《欢迎来到我身边 电影原声专辑》[320K/MP3][105.02MB]
- 群星《欢迎来到我身边 电影原声专辑》[FLAC/分轨][480.9MB]
- 雷婷《梦里蓝天HQⅡ》 2023头版限量编号低速原抓[WAV+CUE][463M]
- 群星《2024好听新歌42》AI调整音效【WAV分轨】
- 王思雨-《思念陪着鸿雁飞》WAV
- 王思雨《喜马拉雅HQ》头版限量编号[WAV+CUE]
- 李健《无时无刻》[WAV+CUE][590M]
- 陈奕迅《酝酿》[WAV分轨][502M]
- 卓依婷《化蝶》2CD[WAV+CUE][1.1G]
- 群星《吉他王(黑胶CD)》[WAV+CUE]
- 齐秦《穿乐(穿越)》[WAV+CUE]
- 发烧珍品《数位CD音响测试-动向效果(九)》【WAV+CUE】
- 邝美云《邝美云精装歌集》[DSF][1.6G]
- 吕方《爱一回伤一回》[WAV+CUE][454M]