Mysql数据库主从配置操作方案

一、主从备份

 

1.热备份的条件

（1） mysql的版本都要高于3.2。

（2） 作为从数据库的数据库版本可以高于主服务器数据库的版本，但是不可以低于主服务器的数据库版本。尽量推荐两者版本相同。

（3） mysql安装目录下存在my.ini配置文件，如果没有请先配置，详见参考二：安装之后没有my.ini配置文件怎么办

2.范例环境：

操作系统： windows7

MySQL版本： 5.7

服务器A的IP: 192.168.68.56

服务器B的IP: 192.168.68.139

3.主从备份实现：

3.1.增加一个用户账号(backup，密码centerm)作为同步用户账号：

A: mysql> Grant replication slave on *.* to backup@'192.168.68.139' identified by '123456';

B: mysql> Grant replication slave on *.* to backup@'192.168.68.56' identified by '123456';

注： (1). mysql> Grant 权限 on . to 用户账号@’允许访问的IP’ identified by ‘用户密码’;

3.2.两个服务器，增加一个数据库(mrrs)，作为同步数据库：

A & B mysql>  create database mrrs;

3.3修改主数据库的my.ini配置

注：如果没有请先配置，详见参考二：安装之后没有my.ini配置文件怎么办：

在原my.ini文件[mysqld]下添加

server-id=1

#设置需要记录log 可以设置log-bin=backuplog 设置日志文件的名称

log-bin=backuplog

#日志文件在mysql数据库文件夹根目录下，mysql将建立不同扩展名，文件名为backuplog的几个日志文件。

#指定需要日志的数据库

binlog-do-db=mrrs

3.4获取file和position字段

完成之后保存，然后使用管理员权限运行命令行重启mysql，否则直接在服务中重新启动mysql

net stop mysql57 

net start mysql57

进入mysql数据库，输入命令：

mysql>show master status;

记一下file，position两个字段（一般file会是mysql-bin.000006）

3.5修改从数据库的my.ini配置

在原my.ini文件[mysqld]下添加

server-id=2

#只同步mrrs库

replicate-do-db=mrrs

relay-log-index=slave-relay-bin.index

relay-log=slave-relay-bin

 

3.6 启动备份：

保存之后重启数据库

进入mysql，

输入命令：

change master to master_host='192.168.68.56',master_user='backup',master_password='123456',master_log_file='mysql-bin.000006',master_log_pos=1606;

mysql-bin.000006→→→之前记录的file

1606→→→之前记录的position

输入命令：

start slave;

输入命令：

show slave status;

3.7完成

在主数据库，建表，查看从数据库是否同步。

3.8一些问题

1. 从数据库不要进行写数据操作，影响复制。

2. 出了错的话要看错误日志，在data目录下的一个.err文件，查看【error】级别日志，然后解决就好啦。

3. 如果slave_IO_running 为connecting 检查IP是否正确，用户名密码是否正确

4. 如果slave_IO_running 为no，检查数据库版本号，看错误日志

5. 如果slave_SQL_Running 为no，检查时候进行了写操作，看错误日志

6. SET GLOBAL log_bin_trust_function_creators = 1;

二、mysql安装之后没有my.ini配置文件怎么办

2.1删除MySql服务

打开cmd（记得”使用管理员身份“打开），如果没有配置环境变量，请cd（切换目录）到mysql程序下的bin文件夹下：

运行命令：

C:\Program Files\MySQL\MySQL Server 5.7\bin>net stop mysql57

C:\Program Files\MySQL\MySQL Server 5.7\bin>sc delete mysql57

"MySql57"为服务名称，你的MysSql服务不一定是这个名称，可以打开电脑的服务窗口查看。

删除完成之后，最好去电脑的服务窗口看下，如果找不到MySql57服务，说明已经已经删除成功。

如果还能看到MySql57服务，可以手动右击选择”停止“，服务停止之后就会自动消失了

 

2.2新建my.ini配置文件

在mysql程序的根目录下(默认是：C:\Program Files\MySQL\MySQL Server 5.7)，新建一个my.ini空白文件，用记事本打开，将以下内容复制进去，保存：

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the

# *** default location during install, and will be replaced if you

# *** upgrade to a newer version of MySQL.

[client]

default-character-set = utf8mb4

[mysql]

default-character-set = utf8mb4

[mysqld]

character-set-client-handshake = FALSE

character-set-server = utf8mb4

collation-server = utf8mb4_bin

init_connect='SET NAMES utf8mb4'

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

# These are commonly set, remove the # and set as required.

basedir = C:\Program Files\MySQL\MySQL Server 5.7

datadir = C:\Program Files\MySQL\MySQL Server 5.7\data

port = 3306

# server_id = .....

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

join_buffer_size = 128M

sort_buffer_size = 16M

read_rnd_buffer_size = 16M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

其中basedir 和 datadir 根据实际MySql安装的位置进行修改，默认是C:\Program Files\MySQL\MySQL Server 5.7。

2.3重新生成data文件

删除之前生成的data文件，如果有重要的数据表，请先备份好，如果已经有了mrrs数据库，请先备份好。

回到cmd，重新生成data文件。运行：

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqld --initialize-insecure --user=mysql

该命令需要执行大概一分钟左右，完成后会在MySql程序文件夹下重新生成名称为data的文件夹：

 

 

2.4重新安装mysql服务，同时绑定my.ini配置文件

安装MySql服务，同时设置绑定my.ini配置文件。命令：

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqld --install "MySql57" --defaults-file="C:/Program Files/MySQL/MySQL Server 5.7/my.ini

“MySql57”是服务名称，57表示5.7版本，当然，也可以自己取别的名字。

”..\my.ini“是新建的配置文件的位置，也可以写成绝对路径”C:\Program Files\MySQL\MySQL Server 5.7\\my.ini“。

如果提示安装成功，这时打开电脑的”服务“窗口，可以找到新添加的MySql57服务：

 

2.5启动服务

这里有两种启动服务的方式：

1. 服务窗口启动
2. cmd启动

使用管理员权限运行cmd命令行重启mysql，否则直接在服务中重新启动mysql

net start mysql5

如果不成功：

 

这时可能是my.ini配置文件中的某些配置有问题。你可以修改ini文件内容，然后从头按步骤再试一遍。

 

2.6重新设置密码

删除了data文件和服务之后，之前的密码就失效了，所以需要重新设置密码。

2.6.1登陆mysql

命令：

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u root -

这时密码为空，不需要填写，直接回车：

2.6.2修改root用户密码

这里有个需要注意的地方，修改root用户密码为123456的命令是：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456'

 如果修改密码不成功不则使用，下面命令：

update mysql.user set authentication_string=password("123456") where user="root";

2.6.3退出MySQL

命令：

mysql> exi

2.6.4 使用修改后的密码重新登陆

这里的命令和之前是一样的，就不写了，密码记得要填刚才设置的，即123456：

现在已经成功绑定了my.ini配置文件了，如果需要自定义配置，可以打开文件进行相应的配置设置，修改后重启服务即可。