Cenots6.4下mysql5.6安装及主从配置

来源：互联网发布：开源数据展示平台编辑：程序博客网时间：2024/05/21 05:39

环境：

Centos6.5

mysql主：10.47.65.76

mysql从：10.47.64.84

一、卸载旧版本

[root@iZbp17vlqunx6kul3no7r4Z data01]# rpm -qa |grep mysql      mysql-libs-5.1.73-3.el6_5.x86_64[root@iZbp17vlqunx6kul3no7r4Z data01]# yum remove mysql-libs

二、安装及配置mysql

2.1 下载三个mysql的rpm软件包

[root@iZbp17vlqunx6kul3no7r4Z data01]# wget http://dev.mysql.com/Downloads/MySQL-5.6/MySQL-server-5.6.21-1.rhel5.x86_64.rpm[root@iZbp17vlqunx6kul3no7r4Z data01]# wget http://dev.mysql.com/Downloads/MySQL-5.6/MySQL-devel-5.6.21-1.rhel5.x86_64.rpm [root@iZbp17vlqunx6kul3no7r4Z data01]# wget http://dev.mysql.com/Downloads/MySQL-5.6/MySQL-client-5.6.21-1.rhel5.x86_64.rpm

2.2 安装三个mysql的rpm软件包（主从操作一致）

[root@iZbp17vlqunx6kul3no7r4Z data01]# ll |grep My-rw-r--r-- 1 root root 23156366 Sep 12  2014 MySQL-client-5.6.21-1.rhel5.x86_64.rpm-rw-r--r-- 1 root root  4579502 Sep 12  2014 MySQL-devel-5.6.21-1.rhel5.x86_64.rpm-rw-r--r-- 1 root root 88524802 Sep 12  2014 MySQL-server-5.6.21-1.rhel5.x86_64.rpm[root@iZbp17vlqunx6kul3no7r4Z data01]# rpm -ivh MySQL-client-5.6.21-1.rhel5.x86_64.rpmPreparing...                ########################################### [100%]   1:MySQL-client           ########################################### [100%][root@iZbp17vlqunx6kul3no7r4Z data01]# rpm -ivh MySQL-devel-5.6.21-1.rhel5.x86_64.rpm Preparing...                ########################################### [100%]   1:MySQL-devel            ########################################### [100%][root@iZbp17vlqunx6kul3no7r4Z data01]# rpm -ivh MySQL-server-5.6.21-1.rhel5.x86_64.rpm Preparing...                ########################################### [100%]   1:MySQL-server           ########################################### [100%]warning: user mysql does not exist - using rootwarning: group mysql does not exist - using root2016-12-09 13:55:20 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).2016-12-09 13:55:20 23284 [Note] InnoDB: Using atomics to ref count buffer pool pages2016-12-09 13:55:20 23284 [Note] InnoDB: The InnoDB memory heap is disabled2016-12-09 13:55:20 23284 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins2016-12-09 13:55:20 23284 [Note] InnoDB: Memory barrier is not used2016-12-09 13:55:20 23284 [Note] InnoDB: Compressed tables use zlib 1.2.3。。。

2.3 建立用户及密码(主从操作一致)

[root@iZbp17vlqunx6kul3no7r4Z data01]# cp /usr/share/mysql/my-default.cnf /etc/my.cnf    #复制配置文件到/etc目录   [root@iZbp17vlqunx6kul3no7r4Z data01]# cat /root/.mysql_secret                           #mysql使用root用户安装生成的密码# The random password set for the root user at Fri Dec  9 13:55:23 2016 (local time): RR6rrCXcEnnOamL2[root@iZbp17vlqunx6kul3no7r4Z data01]# /etc/init.d/mysql start                           #启动mysqlStarting MySQL. SUCCESS![root@iZbp17vlqunx6kul3no7r4Z data01]# mysql -uroot -pRR6rrCXcEnnOamL2                   Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.21Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> mysql> set password=password('1qaz@WSX');                                               #修改密码Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> create database zhucong_test;                                                   #创建测试库Query OK, 1 row affected (0.00 sec)mysql> use zhucong_test                  Database changedmysql> create table run(id int not null,name varchar(32) not null);                    #创建测试表Query OK, 0 rows affected (0.02 sec)mysql> show tables;+------------------------+| Tables_in_zhucong_test |+------------------------+| run                    |+------------------------+1 row in set (0.00 sec)

2.4 主数据库配置

[root@iZbp17vlqunx6kul3no7r4Z mysql]# vim /etc/my.cnf[mysqld]server-id = 1                            #master的IDlog-bin = mysql-bin                      #开启二进制日志binlog-do-db = zhucong_test              #需要同步的库名#binglog-ignore-db = mysql               #不需要同步的库名[root@iZbp17vlqunx6kul3no7r4Z mysql]# /etc/init.d/mysql restartShutting down MySQL.... SUCCESS! Starting MySQL. SUCCESS! mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000004 |      120 | zhucong_test |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)mysql> grant replication slave,reload,super on *.* to backup@'%' identified by '1qaz@WSX';Query OK, 0 rows affected (0.00 sec)#创建backup用户，可以从任何机器上登陆来复制master数据。

2.4 从数据库配置

[root@iZbp17vlqunx6kul3no7r2Z mysql]# vim /etc/my.cnf[mysqld]log_bin = mysql-bin #开启二进制日志，这里不用，如果是主主复制就需要了。server_id = 2 #slave的id，不能和master重复。relay_log = mysql-relay-bin #开启中继日志log_slave_updates = 1 #slave将复制事件写入自己的二进制日志。read_only = 1 #使用只读模式replicate-do-db = zhucong_test： #需要同步的库名mysql> change master to master_host='10.47.65.76',master_user='backup',master_password='1qaz@WSX',master_log_file='mysql-bin.000004',master_log_pos=120;Query OK, 0 rows affected, 2 warnings (0.04 sec)#master_host是主数据库的ip,master_user对应创建的backup用户，master_password对应backup的密码,master_log_file对应master中show master status，展示的file字段，master_log_pos对应position字段。执行成功后，slave就连接上了master。mysql> show slave status\G; #查看slave状态，发现IO和SQL进程都是yes，说明连接成功。*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.47.65.76 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes

mysql> start slave;                                          #上一步还未开启复制数据过程，现在开始数据同步。Query OK, 0 rows affected (0.00 sec)

三、测试mysql主从

在主服务器上进行如下操作：[root@iZbp17vlqunx6kul3no7r4Z mysql]# mysql -uroot -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.6.21-log MySQL Community Server (GPL)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use zhucong_testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from run;Empty set (0.00 sec)mysql> insert into run values(1,'yao');Query OK, 1 row affected (0.00 sec)mysql> insert into run values(2,'kun');Query OK, 1 row affected (0.00 sec)看下从服务器的状态：[root@iZbp17vlqunx6kul3no7r2Z mysql]# mysql -uroot -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 25Server version: 5.6.21-log MySQL Community Server (GPL)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use zhucong_test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from run;+----+------+| id | name |+----+------+|  1 | yao  ||  2 | kun  |+----+------+2 rows in set (0.00 sec)#主从结构配置成功。

四、异常情况模拟

0 0