首先准备两台云服务器
这里我使用的两台全部都安装的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)
创建一个测试用的数据库
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)
主库配置
修改主库的/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
只要以下两个正常即为成功
Slave_IO_Running: Yes(网络正常)；
Slave_SQL_Running: Yes(表结构正常)
这时使用MySQL-Front连接上主库以及从库，在主库插入数据，我们会发现从库出现了一样的数据，修改主库数据从库数据也跟着更新了。
本人小小菜鸟，难免会有错误，希望大神们看到能够及时指出。৳৸ᵃᵑᵏ Ꮍ৹੫ᵎ
随时随地看视频
热门评论
-
song先生2017-08-25 0
查看全部评论写的真心不错，可以看出版主很用心，版主哪里是菜鸟呀，表示发现大神一枚