关闭单实例数据库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和SQL线程;实现主从复制的必要条件是主库一开启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参数使从库只读。