封尘网

让学习成为一种习惯!

以rpm方式安装mysql-5.6.25并配置主从同步

很多小公司刚开始业务发展不是很快使用Mysql作为存储的情况,直接使用主从架构即可满足数据量的需求。一方面可以实现从主切换,减少宕机的风险,同时也可以读写分离提高效率。

系统环境:Centos6.5 2.6.32-431.el6.x86_64
主库:mysql master:[IP:192.168.137.13]
从库:mysql slave: [IP:192.168.137.14]

以下在两台Mysql主机上同样方法操作:

1.检查下linux是不是已经安装了mysql

rpm -qa | grep mysql

如果安装了先卸载旧的版本

yum -y remove mysql...

默认情况下Centos6.5自带一个Mysql库:

[root@mysql-02 ~]# rpm -qa|grep mysql
mysql-libs-5.1.71-1.el6.x86_64

因为版本不同,所以要卸载

2.下载需要的安装包,下载地址:
http://mirrors.sohu.com/mysql/MySQL-5.6/
开始逐个安装

我这里用已经下载过的MySQL-5.6.25-1.el6.x86_64.rpm-bundle.tar包
这个包解压出来就有以下几个安装文件:

MySQL-client-5.6.25-1.el6.x86_64.rpm
MySQL-devel-5.6.25-1.el6.x86_64.rpm
MySQL-embedded-5.6.25-1.el6.x86_64.rpm
MySQL-server-5.6.25-1.el6.x86_64.rpm
MySQL-shared-5.6.25-1.el6.x86_64.rpm
MySQL-shared-compat-5.6.25-1.el6.x86_64.rpm
MySQL-test-5.6.25-1.el6.x86_64.rpm

4.初始化MySQL及设置密码

[root@localhost ]# /usr/bin/mysql_install_db
[root@localhost ]# service mysql start
[root@localhost ]# cat /root/.mysql_secret  #查看root账号密码
# The random password set for the root user at Mon Oct 26 19:41:00 2015 (local time): TNQW_VdjUo4cUbyL

修改密码

[root@localhost ~]# mysql -uroot –pTNQW_VdjUo4cUbyL

mysql> set passwordpassword =password('123456');            #设置密码为123456
Query OK, 0 rows affected (0.05 sec)
mysql> exit

设置开机自启动

chkconfig mysql on
chkconfig --list | grep mysql

MySQL的默认安装位置说明
/var/lib/mysql/ #数据库目录
/usr/share/mysql #配置文件目录
/usr/bin #相关命令目录
/etc/init.d/mysql #启动脚本

Mysql的安装就完成了,两台主机按照上面的操作安装,接下来配置主从同步:

/etc/my.cnf配置参考:从主配置文件这里只有黄色部分修改,主ID比从ID大就好了,一般主为1、从为2;

[root@mysql-02 swper]# cat /etc/my.cnf
[client]
password = 123456
port = 3306
default-character-set=utf8

[mysqld]
port=3306
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
server-id=1
log-bin= /var/lib/mysql/mysql-bin
log-bin-index= /var/lib/mysql/mysql-bin.index
max_connections = 1000
max_connect_errors = 10000
open_files_limit    = 1024
default-storage-engine = InnoDB
tmp_table_size = 2M
relay-log = /var/lib/mysql/relay-bin
relay-log-info-file = /var/lib/mysql/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql
#LOGGING
log_error=/var/log/mysql-error.log
slow_query_log_file=/var/log/mysql-slow.log
slow_query_log=1

[mysql]
default-character-set = utf8

创建主从同步的帐户:允许从192.168.137.0/24网段登陆;
创建同步帐户:rep,密码:rep12345

mysql> grant replication slave on *.* to rep@'192.168.137.%' identified by '12345';
mysql> grant replication slave on *.* to rep@'192.168.137.%' identified by '123456';
Query OK, 0 rows affected (0.07 sec)
mysql> select host,user,password from mysql.user;
+---------------+------+-------------------------------------------+
| host          | user | password                                  |
+---------------+------+-------------------------------------------+
| localhost     | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql-01      | root | *1685971AF0BD4E0D0CBF9DEC23F51B54A0C9BD32 |
| 127.0.0.1     | root | *1685971AF0BD4E0D0CBF9DEC23F51B54A0C9BD32 |
| ::1           | root | *1685971AF0BD4E0D0CBF9DEC23F51B54A0C9BD32 |
| 192.168.137.% | rep  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+------+-------------------------------------------+
5 rows in set (0.00 sec)

接着导出初始数据,目的为了让主从服务器Mysql里的数据一致:

[swper@mysql-01 ~]$ mysqldump -uroot -p'123456' -l -A > all_database.sql

参数说明:-l:小写L,导出数据时锁表,只读;
-A:表示所有数据库,当然可以根据自己情况。如果有-B表示所有表;
然后把这个备份文件传到从服务器上,导入到Mysql里面;

下面在从服务器操作:
导入主服务器备份数据:

[root@mysql-02 swper]# mysql -uroot -p123456 <all_database.sql

停止slave实例

[root@mysql-02 swper]# mysql -uroot -p123456 -e "stop slave"
[root@mysql-02 swper]# cat |mysql -uroot -p123456 <<EOF
change master to
master_host='192.168.137.13',
master_port=3306,
master_user='rep',
master_password='123456',
master_log_file='mysql-bin.000011',
master_log_pos=329;
EOF

接着再启动slave:

[root@mysql-02 swper]# mysql -uroot -p123456 -e "start slave"
[root@mysql-02 swper]# mysql -uroot -p123456 -e "show slave status \G"|grep "Slave"
Warning: Using a password on the command line interface can be insecure.
            Slave_IO_State: Waiting for master to send event
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

两个YES表示主从同步成功:
接下来在主服务器创建数据库、表;就可以在从库上看到更新了;

提醒:本文最后更新于 1504 天前,文中所描述的信息可能已发生改变,请谨慎使用。