my.cnf检查是否正确：mysqld --help --verbose
方案一:
# 设置字符集
[client]
default_character_set=utf8
[mysqld]
character_set_server=utf8
collation_server = utf8_general_ci
# 用户授权[mysql主服务器(master)上创建(IP:192.168.1.1)]
mysql>grant all on *.* to 'user1'@'192.168.1.2' IDENTIFIED BY '123'; #允许user1能从192.168.1.2的服务器登陆到192.168.1.1的服务器中
或
1.mysql>grant all slave on *.* to user1@192.168.1.2 idenitify by '123'
||
2.mysql>grant replication slave on *.* to user1@192.168.1.2 idenitify by '123'
# 查看用户授权表
select user,host,password from mysql.user
# Mysql bin-log日志
开启Mysql bin-log日志
vi /etc/my.cnf
[mysqld]
port=3306
socket=/var/lib/myqsl/mysql.sock
log-slow-queries=mysql-slow.log # 慢查询日志
log-error=mysql.err # mysql错误日志
log=mysql.log
log-bin=mysql-bin # bin-log日志
# 命令查看bin-log是否开启
mysql>show variables like "%bin%";
在显示的选项中有
log_bin ON 说明已经开启了bin-log日志
一但开启后,就会在mysql数据根目录放置
mysql-bin开头的文件
# 与bin-log日志有关的日志刷新
mysql>flush logs;
此时就会多一个最新的bin-log日志
mysql>show master status;
查看最后一个bin日志
mysql>reset master;
清空所有的bin-log日志
--查看binlog日志里面的内容
mysql>mysqlbinlog --no-defaults mysql-00001.bin
用mysqlbinlog mysql-00001.bin时报错,说是字符集有问题
就用mysqlbinlog --no-defaults mysql-00001.bin来查看
用bin来恢复数据
mysqlbinlog --no-defaults mysql-00001.bin|./mysql -uroot -p123 test
# Mysql备份和bin-log日志
1.备份数据
mysqldump -uroot -p123 test -l -F '/web/test.sql'
-l:读锁(所有人将不可以写入操作)
-F:即flush logs,可能重新生成新的日志文件,然后包括log-bin日志
查看binlog日志用mysql>show master status;
# Mysql恢复与bin-log日志
mysql -uroot -p123 test -v -f </web/test.sql
-v:查看导入的详细信息
-f:当中间遇到错误时,可以skip过去,继续执行下面的语句
./mysqlbinlog --no-defaults binlog-file文件|mysql -uroot -p123
恢复binlog-file二进制日志文件
还有别的参数写法
./mysqlbinlog --no-defaults --stop-position="655" binlog-file文件|mysql -uroot -p123
mysqlbinlog后跟主要参数
--stop-position="1000"
--start-position="500"
--stop-date='2013-12-16 21:12:12'
--start-date='2013-12-18 21:13:12'
### Mysql主从复制
# 主服务器配置
1.登陆mysql数据库
mysql -uroot -p123 test
2.给从服务器设置授权用户
mysql>grant all slave on *.* to user1@192.168.10.2 identified by '123' 或
mysql>grant replication slave on *.* to user1@192.168.10.2 identified by '123' 两种写法都是可以的
3.修改主数据库服务器的配置文件my.cnf,开启bin-log日志,并设置server-id的值
server-id=1
4.在主服务器上设置读锁定有效,确保没有数据库操作,以便获得一个一致性的快照:(选做)
mysql>flush tables with read lock;
5.查看主服务器上当前的二进制日志名和偏移量值
mysql>show master status;
File | Position
mysql-bin... 102
6.目前主数据库服务器已经停目了更新操作,生成主数据库的备份,备份的方式有两种:
1).cp全部的数据
2).mysqldump备份数据方法
注:如果主数据库的服务可以停止,那么直势头cp数据文件应该是最快的生成快照的方法
7.主数据库备份完毕后,主数据库可以恢复写操作,剩下的操作只需要在从服务器上去执行
mysql>unlock tables;
8.把主数据库的一致性备份恢复到从数据库上,把以上的压缩包解压后放到相当的目录即可.
scp test.sql root@192.168.10.2:/home/
注:此步骤如果用:
mysqldump -uroot -p123 test -l -F '/web/test.sql' 锁定并生成新的binlog日志文件的话,下面的 4和7步骤可以省去
# 从服务器上
1. reset master
2.恢复一下数据
mysql -uroot -p123 test </home/test.sql
3.配置 [此步骤需要主服务器上要有授权用户]
vi /etc/my.cnf
log-bin=mysql-bin
server-id=2 注:以下的选项上mysql5.5以后就已经移除了这些选项,要在mysql命令行中用
change master to master_host='masterIP', master_user='slave', master_password='slvaePASS';
start slave;
---------------
master-host=192.168.10.1 # 请求同步主Mysql服务器
master-user=slaveuser # 用户名
master-password=123456 # 密码
master-port=3306 # 端口
#master-connect-retry=60 # 间隙60秒
#replicate-do-db=test # 同步的库
#replicate-do-table=test.t1 # 同步的表
4. 启动Mysql数据库服务
5. mysql>show slave statusG
Slave_IO_Running: Yes
Slave_SQL_Running:Yes 这两项为Yes 代表你已经与主服务取得同步了
# 常见错误
1.从数据库无法同步
show slave status显示为Slave_SQL_Running为NO,Seconds_Behind_Master为null
原因:
A.程序可能在slave上进行了写操作
B.也可能是slave机器重启后,事务回滚造成的
解决:方法一:
mysql>slave stop;
mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql>slave start;
方法二:
Slave库
mysql>slave stop
Master库
mysql>show master status;
得到主服务器上当前的二进制日志名和偏移量,然后到slave服务器上执行手动同步
mysql>change master to
master_host="192.168.10.1",
master_user="user",
master_password="123",
master_port=3306,
master_log_file="mysql-bin.0000003",
master_log_pos=98;
启动slave服务
mysql>start slave; 要是不行就slave start;来试试
通过show slave status查看 Slave_SQL_Running为Yes,Seconds_Behind_Master为0即为正常
#################################################################
方案二:
[主数据库服务器]/etc/my.cnf
[mysqld]
server-id=1
log-bin=/var/lib/mysql/mysql-bin.log
mysql>show master status;
msyql>flush tables with read lock; [锁定表]
msyql>exit;
mysqldump -hlocalhsot -uroot -p3306 -p test > /home/test.sql
scp test.sql root@192.168.229.133:/home/
解锁
mysql>unlock tables;
创建用户
msyql>grant replication slave on *.* to 'slaveuser'@'192.168.229.133' identified by '123456'
##### 从服务器上操作
vim /etc/my.cnf
[mysqld]
server-id=2
log-bin=/var/lib/mysql/mysql-bin.log
master-host=192.168.229.131
master-user=slaveuser
master-password=123456
master-port=3306
master-connect-retry=60 #间隙60秒
replicate-do-db=test # 同步的库
保存退出
service msyqld restart
show slave statusG;