mysql授权replication_MYSQL高可用：（replication）

MYSQL高可用：

master MY.CNF

[mysqld]

tmpdir = /tmp

language =.../share/enlish

pid-file =.../run/master.pid

datadir =.../data

server-id = 1

port = 12000

log-bin = .../log/master-bin

socket = /tmp/master.sock

basedir = ...

slave MY.CNF

[mysqld]

tmpdir = /tmp

language =.../share/enlish

pid-file =.../run/master.pid

datadir =.../data

server-id = 2

port = 12001

socket = /tmp/mysqld.sock

basedir = ...

relay-log-index = ...

relay-log = ...

修改slave server_id:

show variables like 'server%';

set global server_id = 22;

确保master log——BIN是开启的

show variables like 'log%';

如果没有开启需要在MY.CNF文件中加入

log-bin=/opt/mysql5149/logbin.log

确保有日志目录有写的权限。

查看日志

show binary logs;

show variables like '%format%';

flush logs;

reset master; 初始化日志

? purge;查看某命令的使用方法

删除日志的方法：

purge binary or master logs to '1.00005';

创建用户并赋予权限；

create user

repl@192.168.0.22 identified by 'what';

grant replication slave on *.* to

repl@192.168.0.22;

show grants for

repl@192.168.0.22;

备份主库：

flush tables with read lock;

show variables like 'datadir';

进入到数据库目录备份想要备份的库

mysqldump -uroot -p -B test | gzip > test.gz

恢复数据库为可写：

unlock tables;

show master status;

删除备库原有的数据库test

drop database test;

恢复数据库：

zcat ./test.gz | mysql -uroot -p

首先在主库执行查看POS 以及二进制日志的编号：

show master status;

在备库执行以下命令：

change master to master_host = '192.168.0.21',master_user='repl',master_password='what',master_log_file='logbin.000002',master_log_pos=308;

启动slave；

start slave；

检查slave工作状态；

show slave status\G

查看以下2部分是否为YES；

slave_IO_Running

Slave_SQL_Running