Linux 安装MySQL及主主同步配置

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/weixin_39816332/article/details/89743028

Linux 安装MySQL

下载Mysql源安装包
1、wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
yum安装mysql源
2、yum localinstall mysql57-community-release-el7-8.noarch.rpm
检查mysql源是否安装成功  
3、 yum repolist enabled | grep "mysql.*-community.*" 
yum安装Mysql
4、 yum install mysql-community-server
启动Mysql服务
5、 systemctl start mysqld
并添加开机启动
systemctl enable mysqld systemctl daemon-reload
查看Mysql状态
6、systemctl status mysqld
查看默认的mysql密码  此时Mysql安装完成!!!接下来修改密码
7、 vi /var/log/mysqld.log
修改mysql的密码
登录mysql: mysql -uroot -p 回车
8、 set password for 'root'@'localhost'=password('Root@3975');

MySQL5.6.6版本之后增加了密码强度验证,如果密码设置简单会拒绝你修改密码
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
如果想修改密码设置规则推荐参考博客:https://blog.51cto.com/zjunzz/2299037

9、添加远程登陆的用户权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Jamie@3975' WITH GRANT OPTION;
FLUSH PRIVILEGES;
10、修改mysql字符编码
vi /etc/my.cnf
添加
character_set_server=utf8
init_connect='SET NAMES utf8'
11 、 重启Mysql
systemctl stop mysqld
systemctl start mysqld
12 、查看Mysql字符集
show variables like '%character%'

安装步骤到此结束。

MySQL主从复制

修改MySQL配置文件 
vi /etc/my.cnf

master配置:

server-id=135
log-bin=mysql-bin
# 主键字段 自增长的长度,跟机器(数据库)的数量一致
auto_increment_increment=2
# 主键自增长是从什么数字开始
auto_increment_offset=1
#binlog-do-db=mstest      //要同步的mstest数据库,要同步多个数据库
#binlog-ignore-db=mysql  //要忽略的数据库

slave配置:

server-id=136
log-bin=mysql-bin
auto-increment-increment=2
# 主键自增长是从什么数字开始 不能和master 一致。避免数据同步冲突问题
auto-increment-offset=2
#replicate-do-db  = wang                 #需要同步的数据库
#binlog-ignore-db  = mysql
#binlog-ignore-db  = information_schema

主从复制我准备了两台虚拟机
192.168.80.135
192.168.80.136

编辑好 my.cnf 文件后 一下操作无论主从都要执行一下

第一步、在master 和 slave mysql添加权限
GRANT REPLICATION SLAVE,FILE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.80.%' IDENTIFIED BY 'Ping@123456';
FLUSH PRIVILEGES;

第二步、再master 和 slave都查看一下状态
show  master status;

在这里插入图片描述
分别在master 和 slave 设置

我的虚拟机ip分别为 192.168.80.135 设为master  192.168.80.136 设为 slave
master_host='另一台虚拟机的ip'  
master_user='第一步创建的用户'
master_password='第一步创建的用户的密码'
master_log_file='上一步的file文件 另一台虚拟机上的’
master_log_pos=另一台虚拟机Position数;

第三步、 在主设置slave信息
change master to master_host=‘192.168.80.136’,master_user=‘repluser’,master_password=‘Ping@123456’,master_log_file=‘mysql-bin.000001’,master_log_pos=640;

在从设置master信息
change master to master_host='192.168.80.135',master_user='repluser',master_password='Ping@123456',master_log_file='mysql-bin.000001',master_log_pos=640;

第四步、 开启slave,启动SQL和IO线程
start slave;

第五步、查看slave的状态
show slave status\G

在这里插入图片描述
主从配置完成。

展开阅读全文

MYSQL 循环同步

12-13

最近同步问题比较多。我自己做了一下实验。rn刚刚抽空做了一下MYSQL 的主主同步。rn把步骤写下来,至于会出现的什么问题,以后随时更新。这里我同步的数据库是TESTrn1、环境描述。rn 主机:192.168.0.231(A)rn 主机:192.168.0.232(B)rn MYSQL 版本为5.1.21rn2、授权用户。rnA:rnmysql> grant replication slave,file on *.* to 'repl1'@'192.168.0.232' identifiedrn by '123456';rnQuery OK, 0 rows affected (0.00 sec)rnrnmysql> flush privileges;rnQuery OK, 0 rows affected (0.00 sec)rnB:rnmysql> grant replication slave,file on *.* to 'repl2'@'192.168.0.231' identifiedrn by '123456';rnQuery OK, 0 rows affected (0.00 sec)rnrnmysql> flush privileges;rnQuery OK, 0 rows affected (0.00 sec)rn然后都停止MYSQL 服务器。rnrn3、配置文件。rn在两个机器上的my.cnf里面都开启二进制日志 。rnA:rnuser = mysqlrnlog-bin=mysql-binrnserver-id = 1rnbinlog-do-db=testrnbinlog-ignore-db=mysqlrnreplicate-do-db=testrnreplicate-ignore-db=mysqlrnlog-slave-updatesrnslave-skip-errors=allrnsync_binlog=1rnrnB:rnuser = mysqlrnlog-bin=mysql-binrnserver-id = 2rnbinlog-do-db=testrnbinlog-ignore-db=mysqlrnreplicate-do-db=testrnreplicate-ignore-db=mysqlrnlog-slave-updatesrnslave-skip-errors=allrnsync_binlog=1rn至于这些参数的说明具体看手册。rn红色的部分非常重要,如果一个MASTER 挂掉的话,另外一个马上接管。rn紫红色的部分指的是服务器频繁的刷新日志。这个保证了在其中一台挂掉的话,日志刷新到另外一台。从而保证了数据的同步 。rn4、重新启动MYSQL服务器。rn在A和B上执行相同的步骤rn[root@localhost ~]# /usr/local/mysql/bin/mysqld_safe &rn[1] 4264rn[root@localhost ~]# 071213 14:53:20 mysqld_safe Logging to '/usr/local/mysql/data/localhost.localdomain.err'.rn/usr/local/mysql/bin/mysqld_safe: line 366: [: -eq: unary operator expectedrn071213 14:53:20 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/datarnrn5、进入MYSQL的SHELL。rnA:rnmysql> flush tables with read lock\GrnQuery OK, 0 rows affected (0.00 sec)rnrnmysql> show master status\Grn*************************** 1. row ***************************rn File: mysql-bin.000007rn Position: 528rn Binlog_Do_DB: testrnBinlog_Ignore_DB: mysqlrn1 row in set (0.00 sec)rnrnB:rnmysql> flush tables with read lock;rnQuery OK, 0 rows affected (0.00 sec)rnrnmysql> show master status\Grn*************************** 1. row ***************************rn File: mysql-bin.000004rn Position: 595rn Binlog_Do_DB: testrnBinlog_Ignore_DB: mysqlrn1 row in set (0.00 sec)rn然后备份自己的数据,保持两个机器的数据一致。rn方法很多。完了后看下一步。rn6、在各自机器上执行CHANGE MASTER TO命令。rnA:rnmysql> change master torn -> master_host='192.168.0.232',rn -> master_user='repl2',rn -> master_password='123456',rn -> master_log_file='mysql-bin.000004',rn -> master_log_pos=595;rnQuery OK, 0 rows affected (0.01 sec)rnmysql> start slave;rnQuery OK, 0 rows affected (0.00 sec)rnrnrnB:rnmysql> change master torn -> master_host='192.168.0.231',rn -> master_user='repl1',rn -> master_password='123456',rn -> master_log_file='mysql-bin.000007',rn -> master_log_pos=528;rnQuery OK, 0 rows affected (0.01 sec)rnmysql> start slave;rnQuery OK, 0 rows affected (0.00 sec)rnrn7、查看各自机器上的IO进程和 SLAVE进程是否都开启。rnA:rnrnmysql> show processlist\Grn*************************** 1. row ***************************rn Id: 2rn User: replrn Host: 192.168.0.232:54475rn db: NULLrnCommand: Binlog Dumprn Time: 1590rn State: Has sent all binlog to slave; waiting for binlog to be updatedrn Info: NULLrn*************************** 2. row ***************************rn Id: 3rn User: system userrn Host:rn db: NULLrnCommand: Connectrn Time: 1350rn State: Waiting for master to send eventrn Info: NULLrn*************************** 3. row ***************************rn Id: 4rn User: system userrn Host:rn db: NULLrnCommand: Connectrn Time: 1149rn State: Has read all relay log; waiting for the slave I/O thread to update itrn Info: NULLrn*************************** 4. row ***************************rn Id: 5rn User: rootrn Host: localhostrn db: testrnCommand: Queryrn Time: 0rn State: NULLrn Info: show processlistrn4 rows in set (0.00 sec)rnrnB:rnrnmysql> show processlist\Grn*************************** 1. row ***************************rn Id: 1rn User: system userrn Host:rn db: NULLrnCommand: Connectrn Time: 2130rn State: Waiting for master to send eventrn Info: NULLrn*************************** 2. row ***************************rn Id: 2rn User: system userrn Host:rn db: NULLrnCommand: Connectrn Time: 1223rn State: Has read all relay log; waiting for the slave I/O thread to update itrn Info: NULLrn*************************** 3. row ***************************rn Id: 4rn User: rootrn Host: localhostrn db: testrnCommand: Queryrn Time: 0rn State: NULLrn Info: show processlistrn*************************** 4. row ***************************rn Id: 5rn User: repl2rn Host: 192.168.0.231:50718rn db: NULLrnCommand: Binlog Dumprn Time: 1398rn State: Has sent all binlog to slave; waiting for binlog to be updatedrn Info: NULLrn4 rows in set (0.00 sec)rnrn如果红色部分没有出现,检查DATA目录下的错误文件。rnrn8、释放掉各自的锁,然后进行插数据测试。rnmysql> unlock tables;rnQuery OK, 0 rows affected (0.00 sec)rnrnrn 论坛

没有更多推荐了,返回首页