首页手记【MySQL】数据库主从同步安装配置日记

【MySQL】数据库主从同步安装配置日记

标签：

MySQL

首先准备两台云服务器

这里我使用的两台全部都安装的Debian

主库的ip：45.76.146.*

从库的ip：118.89.155.*

1.安装MySQL（两台服务器执行一样的cao'zuo）

使用以下命令默认一路安装

apt-get update
apt-get install mysql-server mysql-client

安装完成后注意去/etc/mysql/my.ini修改编码以及连接权限

注释掉以下语句，不然远程无法连接

#bind-address       = 127.0.0.1

然后修改编码，在各个节点下添加以下代码

[client]
default-character-set=utf8
[mysqld_safe]
default-character-set=utf8
[mysqld]
character_set_server=utf8  
init_connect='SET NAMES utf8' 
[mysql]
default-character-set=utf8

保存后重启MySQL

service mysql restart

然后登陆MySQL，查看编码格式，发现已经全部修改为utf-8

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

2.创建远程连接用户以及测试用的数据库（两台服务器执行一样的操作）

创建一个测试用的数据库

mysql> create database yezi_test;
Query OK, 1 row affected (0.00 sec)

创建一个可以在任意地方连接数据库的用户

mysql> create user 'yezi'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

赋予用户测试数据库的全部权限

mysql> grant all on yezi_test.* to 'yezi'@'%';
Query OK, 0 rows affected (0.00 sec)

3.配置主从

主库配置

修改主库的/etc/mysql/my.ini配置文件

[mysqld]
log-bin=mysql-bin
server-id=1
binlog-ignore-db=information_schema
#这个是指忽略的数据库
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-do-db=yezi_test
#这个是要执行的数据库yezi_test

重启数据库使配置生效

service mysql restart

然后连接数据库，锁主库表

mysql> flush tables with read lock;

显示主库信息，记录File和Position，从库设置将会用到（这里我们的记录的信息以实际显示的为准）

mysql> show master status;
+------------------+----------+--------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000095 |      107 | yezi_test    | information_schema,mysql,performance_schema |
+------------------+----------+--------------+---------------------------------------------+
1 row in set (0.00 sec)

从库配置

在从服务器验证连接主库，如下成功连接

root@VM-188-254-debian:/etc# mysql -h 45.76.146.* -u yezi -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.5.55-0+deb8u1-log (Debian)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit;

然后将主库备份，导入从库，解锁主库表（对应上面锁主库表）

mysql> unlock tables;

修改从库的/etc/mysql/my.ini配置文件

[mysqld]
log-bin=mysql-bin
server-id=2
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
replicate-do-db=yezi_test
#我们要同步的数据库yezi_test
replicate-ignore-db=mysql

重启从库，然后登陆从库

设置连接MASTER MASTER_LOG_FILE为主库的File，MASTER_LOG_POS为主库的Position
注意下面第二条命令语句中的master_log_file='mysql-bin.000001', master_log_pos=98; 对应为前面在主库中执行的show master status;的结果

mysql> slave stop;
Query OK, 0 rows affected (0.28 sec)

mysql> change master to master_host='45.76.146.*',master_user='yezi',master_password='123456',master_log_file='mysql-bin.000095', master_log_pos=107;
Query OK, 0 rows affected (0.06 sec)

mysql> slave start;            
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

只要以下两个正常即为成功