2015.07.21 一、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 <table border="1" cellspacing="0" cellpadding="0" width="596" style="width:447.2pt;border:none;"> <tbody> <tr> <td valign="top" style="border:solid windowtext 1.0pt;"> [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   </td> </tr> </tbody> </table> 从机my.cnf 配置(参考上面的) <table border="1" cellspacing="0" cellpadding="0" style="border:none;"> <tbody> <tr> <td width="437" valign="top" style="border:solid windowtext 1.0pt;">   [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  </td> </tr> </tbody> </table> 从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; <div> <div class="codeheads"> 点击(此处)折叠或打开 </div> <div id="code56" class="codeText"> <ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"> <li> *************************** 1. row *************************** </li> <li>                Slave_IO_State: Waiting for master to send event </li> <li>                   Master_Host: 10.139.52.164 </li> <li>                   Master_User: ideal </li> <li>                   Master_Port: 3306 </li> <li>                 Connect_Retry: 60 </li> <li>               Master_Log_File: mysql-master-bin.000007 </li> <li>           Read_Master_Log_Pos: 1036536095 </li> <li>                Relay_Log_File: wmback-relay-bin.000011 </li> <li>                 Relay_Log_Pos: 1036536265 </li> <li>         Relay_Master_Log_File: mysql-master-bin.000007 </li> <li>              Slave_IO_Running: Yes </li> <li>             Slave_SQL_Running: Yes </li> <li>               Replicate_Do_DB: </li> <li>           Replicate_Ignore_DB: </li> <li>            Replicate_Do_Table: </li> <li>        Replicate_Ignore_Table: </li> <li>       Replicate_Wild_Do_Table: </li> <li>   Replicate_Wild_Ignore_Table: </li> <li>                    Last_Errno: 0 </li> <li>                    Last_Error: </li> <li>                  Skip_Counter: 0 </li> <li>           Exec_Master_Log_Pos: 1036536095 </li> <li>               Relay_Log_Space: 1036536493 </li> <li>               Until_Condition: None </li> <li>                Until_Log_File: </li> <li>                 Until_Log_Pos: 0 </li> <li>            Master_SSL_Allowed: No </li> <li>            Master_SSL_CA_File: </li> <li>            Master_SSL_CA_Path: </li> <li>               Master_SSL_Cert: </li> <li>             Master_SSL_Cipher: </li> <li>                Master_SSL_Key: </li> <li>         Seconds_Behind_Master: 0 </li> <li> Master_SSL_Verify_Server_Cert: No </li> <li>                 Last_IO_Errno: 0 </li> <li>                 Last_IO_Error: </li> <li>                Last_SQL_Errno: 0 </li> <li>                Last_SQL_Error: </li> <li>   Replicate_Ignore_Server_Ids: </li> <li>              Master_Server_Id: 1 </li> <li>                   Master_UUID: 10162a19-2c75-11e5-af6c-00163e0007a0 </li> <li>              Master_Info_File: /mydata/mysql/master.info </li> <li>                     SQL_Delay: 0 </li> <li>           SQL_Remaining_Delay: NULL </li> <li>       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it </li> <li>            Master_Retry_Count: 86400 </li> <li>                   Master_Bind: </li> <li>       Last_IO_Error_Timestamp: </li> <li>      Last_SQL_Error_Timestamp: </li> <li>                Master_SSL_Crl: </li> <li>            Master_SSL_Crlpath: </li> <li>            Retrieved_Gtid_Set: </li> <li>             Executed_Gtid_Set: </li> <li>                 Auto_Position: 0 </li> <li> 1 row in set (0.00 sec) </li> <li> </li> <li> ERROR: </li> <li> No query specified </li> </ol> </div> </div> Slave_IO_Running: Yes Slave_SQL_Running: Yes 看到两个Yes 表示主从搭建成功，可以测试创建数据库，表并插入数据，我这里是成功的。

ITPub博客

rpm 安装5.6 MySql主从搭建

最新文章