rpm 安装5.6 MySql主从搭建 (转)

一、MySQL服务器安装

1.       添加用户：

groupadd mysql

useradd -r -g mysql mysql

2.       上传rpm包
注：Oracle官方提供的最新MySQL安装包，目前没有更高版本的，有可能其他版本还够稳定，所以Oracle没有提供

MySQL-client-advanced-5.6.25-1.el6.x86_64.rpm   

MySQL-server-advanced-5.6.25-1.el6.x86_64.rpm        

MySQL-test-advanced-5.6.25-1.el6.x86_64.rpm

MySQL-devel-advanced-5.6.25-1.el6.x86_64.rpm    

MySQL-shared-advanced-5.6.25-1.el6.x86_64.rpm

MySQL-embedded-advanced-5.6.25-1.el6.x86_64.rpm  
MySQL-shared-compat-advanced-5.6.25-1.el6.x86_64.rpm

3.       安装rpm包

注：安装rpm 包的时候，要按照顺序，shared-compat 需要再server前安装，不然会报错

rpm –ivh  MySQL-shared-compat-advanced-5.6.25-1.el6.x86_64.rpm

rpm –ivh  MySQL-client-advanced-5.6.25-1.el6.x86_64.rpm   

rpm –ivh  MySQL-test-advanced-5.6.25-1.el6.x86_64.rpm  #这个可以不用安装，我每次都没有安装

rpm –ivh  MySQL-devel-advanced-5.6.25-1.el6.x86_64.rpm    

rpm –ivh  MySQL-shared-advanced-5.6.25-1.el6.x86_64.rpm

rpm –ivh  MySQL-embedded-advanced-5.6.25-1.el6.x86_64.rpm

rpm –ivh  MySQL-server-advanced-5.6.25-1.el6.x86_64.rpm   

4.       修改datadir 目录：

1). service mysql stop    #修改MySQL配置文件，参数文件最好是再MySQL停止的情况修改

2). mv /mysqldata/mysql/*  /mydata/mysql/

              chown –R mysql.mysql /mydata/mysql/*   #root 用户移动目录后，权限会被修改，所以要授予MySQL用户权限，不然启动MySQL会报错

3). vi /etc/init.d/mysql

原：

# Set some defaults

mysqld_pid_file_path=

if test -z “$basedir”

then

  basedir=/usr

  bindir=/usr/bin

  if test -z “$datadir”

  then

    datadir=/var/lib/mysql

修改后：

# Set some defaults

mysqld_pid_file_path=

if test -z “$basedir”

then

  basedir=/usr

  bindir=/usr/bin

  if test -z “$datadir”

  then

datadir=/mysqldata/mysql  #根据个人的规划修改

4). vi /etc/my.cnf

datadir=/mysqldata/mysql

socket = /mysqldata/mysql/mysql.sock #sock 文件一般是放在tmp下面，我这里跟数据文件放在一起

5). service mysql start

以上修改MySQL数据目录需要主从机上都需做。

 

二、主从配置步骤

1.       主从机修改配置文件；

2.       从机指向主机，开启接收。

主my.cnf

[client] port = 3306 socket = /mysqldata/mysql/mysql.sock  #客户端使用的进程通道文件 [mysqld] server-id=1 #主从中节点的唯一ID log-bin=mysql-master-bin  #二进制日志名字，用于主从复制 binlog_format = mixed #binlog日志格式，mysql默认采用statement，建议使用mixed expire_logs_days=30  #二进制日志保留的时间 max_connections=1000 #最大连接数 innodb_flush_log_at_trx_commit=1 #每隔1秒将数据写入文件InnoDB默认1 sync_binlog=1 #主从同步模式 1表示实时同步 binlog-do-db=pwapacydb #需要做复制的数据库名 #binlog-ignore-db=mysql,test,information_schema #二进制日志过滤条件 innodb_buffer_pool_size = 20480M #根据实际内存 <65% skip-name-resolve  #对局域网各服务器的IP做了反向解析 加速访问速度 skip-external-locking   #加上可以避免因资源锁引起MySQL服务停止 datadir = /mysqldata/mysql  #数据文件存放目录 port = 3306  #MySQL访问端口 socket = /mysqldata/mysql/mysql.sock sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

从机my.cnf 配置(参考上面的)

[client] port = 3306 socket = /mysqldata/mysql/mysql.sock [mysqld] server-id=2 datadir = /mysqldata/mysql port = 3306 socket = /mysqldata/mysql/mysql.sock user=mysql log-bin=mysql-slave-bin max_binlog_size=200M binlog_format = mixed expire_logs_days=30 innodb_flush_log_at_trx_commit=1 sync_binlog=1 read_only=1 replicate-do-db   =test   innodb_buffer_pool_size = 20480M skip-name-resolve skip-external-locking sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

从my.cnf

3.       主从机创建同步用户授权：

注：主从都建用户是为了主从可以相互切换，IP指向对方，如果只在从上面做，则配置为主从复制，如果主从机都指向，则是主主复制

mysql> grant replication slave on *.* to ‘zhanglin’@’172.16.11.87’ identified by ‘zhanglin’;

mysql> flush privileges;

mysql> show master status;

4.       从机指向主机:

mysql> change master to master_host=’172.0.0.1′,

master_user=’zhanglin’,

master_password=’zhanglin’,

master_log_file=’mysql-master-bin.000007′, #主机上当前的二进制日志名字

master_log_pos=1036536265;  #在主机上，show master status 可以看到二进制日志的位置

5.       mysql> start slave;  #启动从库连接

6.       mysql> show slave status\G;  #查看连接情况 

mysql> show slave status\G;

 

点击(此处)折叠或打开

 

1. *************************** 1. row ***************************
2.                Slave_IO_State: Waiting for master to send event
3.                   Master_Host: 10.139.52.164
4.                   Master_User: ideal
5.                   Master_Port: 3306
6.                 Connect_Retry: 60
7.               Master_Log_File: mysql-master-bin.000007
8.           Read_Master_Log_Pos: 1036536095
9.                Relay_Log_File: wmback-relay-bin.000011
10.                 Relay_Log_Pos: 1036536265
11.         Relay_Master_Log_File: mysql-master-bin.000007
12.              Slave_IO_Running: Yes
13.             Slave_SQL_Running: Yes
14.               Replicate_Do_DB: 
15.           Replicate_Ignore_DB: 
16.            Replicate_Do_Table: 
17.        Replicate_Ignore_Table: 
18.       Replicate_Wild_Do_Table: 
19.   Replicate_Wild_Ignore_Table: 
20.                    Last_Errno: 0
21.                    Last_Error: 
22.                  Skip_Counter: 0
23.           Exec_Master_Log_Pos: 1036536095
24.               Relay_Log_Space: 1036536493
25.               Until_Condition: None
26.                Until_Log_File: 
27.                 Until_Log_Pos: 0
28.            Master_SSL_Allowed: No
29.            Master_SSL_CA_File: 
30.            Master_SSL_CA_Path: 
31.               Master_SSL_Cert: 
32.             Master_SSL_Cipher: 
33.                Master_SSL_Key: 
34.         Seconds_Behind_Master: 0
35. Master_SSL_Verify_Server_Cert: No
36.                 Last_IO_Errno: 0
37.                 Last_IO_Error: 
38.                Last_SQL_Errno: 0
39.                Last_SQL_Error: 
40.   Replicate_Ignore_Server_Ids: 
41.              Master_Server_Id: 1
42.                   Master_UUID: 10162a19-2c75-11e5-af6c-00163e0007a0
43.              Master_Info_File: /mydata/mysql/master.info
44.                     SQL_Delay: 0
45.           SQL_Remaining_Delay: NULL
46.       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
47.            Master_Retry_Count: 86400
48.                   Master_Bind: 
49.       Last_IO_Error_Timestamp: 
50.      Last_SQL_Error_Timestamp: 
51.                Master_SSL_Crl: 
52.            Master_SSL_Crlpath: 
53.            Retrieved_Gtid_Set: 
54.             Executed_Gtid_Set: 
55.                 Auto_Position: 0
56. 1 row in set (0.00 sec)
57.  
58. ERROR: 
59. No query specified

 

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

看到两个Yes 表示主从搭建成功，可以测试创建数据库，表并插入数据，我这里是成功的。

 

转至：http://yiyulinfeng.com/2015/07/21/rpm-%E5%AE%89%E8%A3%855-6-mysql%E4%B8%BB%E4%BB%8E%E6%90%AD%E5%BB%BA/