mysql多实例配置和主从配置

关闭单实例数据库killall mysqld、/etc/init.d/mysqld stop 、chkconfig --listmysqld  chkconfig mysqld off

创建目录 mkdir -p /data/{3306,3307}/{data,logs}；             测试：tree /data

简单配置双实例：

配置文件3306：cat/data/3306/my.cnf

[client]

port = 3306

socket = /data/3306/data/mysql.sock

 

[mysqld]

port=3306

socket = /data/3306/data/mysql.sock

pid-file = /data/3306/data/mysql.pid

basedir = /application/mysql

datadir = /data/3306/data

server-id=1

log-bin=mysql-bin

log-bin-index= mysql-bin.index

 

# LOGGING

log_error=/data/3306/logs/mysql-error.log  

slow_query_log_file=/data/3306/logs/mysql-slow.log

slow_query_log=1

3307与3306配置文件my.cnf就server-id =1 、3不同，其他3306改3307就可以

3306启动文件【3307改个端口就可以】： cat /data/3306/mysql

#!/bin/sh

port=3306

mysql_user="root"

mysql_pwd=""

CmdPath="/usr/local/mysql/bin"

#startup function

function_start_mysql()

{

   printf "Starting MySQL...\n"

   /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf2>&1 > /dev/null &

}

#stop function

function_stop_mysql()

{

   printf "Stoping MySQL...\n"

   ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S/data/${port}/mysql.sock shutdown

}

#restart function

function_restart_mysql()

{

   printf "Restarting MySQL...\n"

   function_stop_mysql

   sleep 2

   function_start_mysql

}

case $1 in

start)

   function_start_mysql

;;

stop)

   function_stop_mysql

;;

restart)

   function_restart_mysql

;;

*)

   printf "Usage: /data/${port}/mysql {start|stop|restart}\n"

esac

 

赋予权限： chown -R mysql:mysql /data

改权限：find /data -name mysql -exec chmod 700 {} \;

配置环境变量：export PATH=$PATH:/application/mysql/bin：$PATH

初始化数据库3306|3307：/application/mysql/scripts/mysql_install_db--defaults-file=/data/3306/my.cnf --user=mysql  --basedir=/application/mysql--datadir=/data/3306/data

 

启动数据库： /data/3306/mysql start                                     /data/3307/mysqlstart

查看有内容：netstat -lnt|grep 330

加入到自启服务：echo "#mysql multi instances" >>/etc/rc.local

echo "/data/3306/mysql start">>/etc/rc.local                                  echo"/data/3307/mysql start" >>/etc/rc.local

登录数据库： mysql -S /data/3306/date/mysql.sock

mysql> show databases;                                                        mysql>create database oldboy3306;

【3307】 mysql -S /data/3307/date/mysql.sock

改密码：  mysqladmin -u root -S/data/3306/data/mysql.sock password '123456’

重新登录：mysql -uroot -p -S /data/3306/data/mysql.sock         输入密码：Enter password:

mysql> show databases;

 

Mysql主从复制【主Master、从Slave】

一主多从、双主双向、线性级联单向双主、环状级联单向多主同步【单向、双向、链式级联、环状】

应用场景：从服务器作为主服务器的实时数据备份、主从服务器实现读写分离，从服务器实现负载均衡、把多个服务器更具业务重要性进行拆分访问

Mysql主从读写分离的方案：通过程序【性能和效率最佳】、开源的软件、大型门户独立开发的DAL层综合软件

原理【异步复制】介绍：主从复制是异步的逻辑的SQL语句级的复制；复制时，主库有一个I/O线程，从库有两个线程，即I/O和ＳＱＬ线程；实现主从复制的必要条件是主库一开启binlog功能；作为复制的所有MYSQL节点的server-id都不能相同；binlog文件只记录对数据库有更改的SQL语句(来自主数据库内容的变更)，不记录任何查询语句。

 

实践：定义主库Master：192.168.50.128，从库Slave：192.168.50.129

在主库上进行以下操作Master192.168.50.128

在my.cnf文件添加内容：

[mysqld] server-id = 1【设置服务器id，为1表示主服务器，其他基本用IP最后以为数字表示】log-bin= /data/3306/mysql-bin【mysql-bin表示：启动MySQ二进制日志系统】

重启服务mysql restart

【选择添加：

binlog-do-db=osyunweidb  #需要同步的数据库名，如果有多个数据库，可重复此参数，每个一行。

binlog-ignore-db=mysql   #不同步mysql系统数据库】

登录mysql查看binlog功能是否开启：show variables like ‘log_bin’;

 

再主库上建立用于主从复制的帐号并授权：  

建立帐号lhs：只能从从库Slave这个IP访问主服务器主库Master上面的数据库

grant replication slave  on *.* to 'lhs'@'192.168.50.128' identifiedby '123456' ；

【说明：.表示所有库所有表，192.168.50。128以lhs用户访问】

登录多实例数据库3306：mysql –uroot –p’123456’ –s /data/3306/mysql.sock

grant replication slave  on *.* to 'lhs'@'10.0.0.%' identified by'123456'；【%整个网段】

flush privileges;   【刷新系统授权表，是授权生效】

检查主库创建的lhs复制帐号命令及结果如下：

select user,host from mysql.user[或者加whereuser=’lhs’];

数据库只读锁定命令，防止导出数据库的时候有数据写入:flush table with read lock;

锁表后查看主库状态：show master status;【这里记住File的值和Position的值，后面会用到。】

 

把主库同步之前的数据库导入从库

导出主数据之前的数据到backup：mysqldump –uroot –p’123456’ --events –A –B |gzip >/home/backup/mysql_bak.$(date+%F).sql.gz【-A表示备份所有库，-B表示增加usb DB和drop等】

把从主数据库倒出来的数据恢复到从库【解压】：gzid –d mysql_bak.2016-01-07.sql.gz

mysql –uroot –p123456< mysql_bak.2016-01-07.sql

【导出指定数据库osyunweidb：mysqldump -u root -p osyunweidb > /home/osyunweidb.sql

导入数据库到MySQL从服务器：进入从服务器MySQL控制台创建数据库create database osyunweidb; 进入数据库use osyunweidb; 导入备份文件到数据库source/home/osyunwe idb.sql】

导完数据后解锁主库恢复读写：unlock tables;

 

在MYSQL从库执行以下操作Slave192.168.50.129：

修改my.cnf文件mysqld模块：server_id = 2；log-bin = mysql-bin

登录数据库查看这这两个值状态：show variables like ‘log_bin或server_id’

登录从库Slave mysql控制台，配置复制参数【不能有空格，执行同步语句】

CHANGE MASTER TO

MASTER_HOST='192.168.50.128',【这是主库的IP】

MASTER_PORT=3306，

MASTER_USER='lhs',【用户名】

MASTER_PASSWORD='123456',【密码】

MASTER_LOG_FILE='mysql-bin.000008',【show masterstatus命令后日志文件名称file的值】

MASTER_LOG_POS=2091; 【show masterstatus命令看到的二进制日志偏移量】【中间逗号隔开】

查看写入的信息文件master.info：cat /data/master.info

 

登录mysql从库启动主从复制开关：start slave;【开启slave同步进程】

show slave status\G;【 查看slave同步信息，出现以下内容】

注意查看：Slave_IO_Running: Yes                          Slave_SQL_Running:Yes

以上这两个参数的值为Yes，即说明配置成功

 

测试MySQL主从服务器双机热备是否成功

1、进入MySQL主服务器              use osyunweidb   #进入数据库

CREATE TABLE test ( id int not null primarykey,name char(20) );   #创建test表

2、进入MySQL从服务器               use osyunweidb   #进入数据库

show tables;  #查看osyunweidb表结构，会看到有一个新建的表test，表示数据库同步成功

 

Mysql主从复制配置步骤小结：

准备两台数据库环境或者单台多实例环境，确定能正常启动和登陆。

配置my.cnf文件：主库配置log-bin和server-id参数；从库配置server-id，该值不能和主库和其他库一样，一般不开启从库log-bin功能，配置后重启服务

登录主库，增加从库连接主库同步的账号，并授权replication slave 同步的权限；

登录主库，整库锁表（窗口关闭后或超时失效），然后show master status 查看binlog的位置状态。

新建窗口，在linux命令行备份导出原有的数据库数据，并拷贝到从库所在的服务器目录。如果数据库数据量很大，并且允许停机，可以停机打包，而不用mysqldump.

导出主库数据后，执行unlock tables解锁主库，把主库导出的数据恢复到从库。

根据主库的show master status查看到的binlog的位置状态，在从库中执行change master to…… 语句。

从库开启复制开关，即执行start slave;.

从库show slave status\G;检查同步状态，并在主库进行更新测试.

 

Mysql主从复制读写分离集群【增、删、改、查insert、deleted、update、select】

主库对web_w用户授权如下：GRANT SELECT,INSERT,UPDATE,DELETE ON ‘web’.* TO ‘web_w’@ ’192.168.50.%’indentifiedby ‘123456’;

从库对web_r用户授权如下:GRANT SELECT ON ‘web’.*TO‘web_r’@ ’192.168.50.%’ indentified by ‘123456’;

主从同一用户[wed]使用revoke收回对应的权限：revoke INSERT,UPDATE,DELETE ON ‘web’.*from ‘web’@ ’192.168.50.%’;

忽略授权库mysql同步，主库的配置如下：blnlog-ignore-db = mysql replicate-ignore-db= mysql

可以使用read-only参数使从库只读。