mysql主从简单配置

环境

hostname

ip

端口

master

128.0.0.101

3306

slave

128.0.0.102

3306

安装mysql

[root@master ~]# yum -y install mysql mysql-server mysql-devel
[root@slave ~]# yum -y install mysql mysql-server mysql-devel

启动mysql

[root@master ~]# service mysqld start
Initializing MySQL database:  WARNING: The host 'master' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h master password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

                                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

[root@slave ~]# service mysqld start
Initializing MySQL database:  WARNING: The host 'slave' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h slave password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

                                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

修改密码登陆

[root@master ~]# mysqladmin -u root password '123456'
[root@master ~]# mysql -uroot -p123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, 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> 

[root@slave ~]# mysqladmin -u root password '123456'
[root@slave ~]# mysql -uroot -p123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, 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>

主数据库

创建测试数据库

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

mysql> use HA;
Database changed
mysql> 
mysql> create table T1(id int,name varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into T1 values (1,'a'),(2,'b');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

停数据库

[root@master ~]# service mysqld stop
Stopping mysqld:                                           [  OK  ]

修改配置文件

[root@master ~]# vim /etc/my.cnf 
[mysqld]
log-bin=mysql-bin-master
server-id=1

启动数据库

[root@master ~]# service mysqld start
Starting mysqld:                                           [  OK  ]

授权从服务器访问刷新权限

[root@master ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to slave@128.0.0.102 identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查看master信息

mysql> show master status \G
*************************** 1. row ***************************
            File: mysql-bin-master.000001
        Position: 330
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

备份需要同步的数据库

[root@master ~]# mysqldump -uroot -p123456 HA>HA.sql

复制主数据到从服务器上

[root@master ~]# scp HA.sql root@128.0.0.102:/root/

查看事件

mysql> show binlog events;
+-------------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------+
| Log_name                | Pos | Event_type  | Server_id | End_log_pos | Info                                                                       |
+-------------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------+
| mysql-bin-master.000001 |   4 | Format_desc |         1 |         106 | Server ver: 5.1.73-log, Binlog ver: 4                                      |
| mysql-bin-master.000001 | 106 | Query       |         1 |         255 | grant replication slave on *.* to slave@128.0.0.102 identified by '123456' |
| mysql-bin-master.000001 | 255 | Query       |         1 |         330 | flush privileges                                                           |
+-------------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------+
3 rows in set (0.00 sec)

从数据库

从数据库创建数据库

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

导入数据库

[root@slave ~]# mysql -uroot -p123456 HA<HA.sql

测试数据库连通性

[root@slave ~]# mysql -uslave -p123456 -h 128.0.0.101
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, 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.

关掉服务

[root@slave ~]# service mysqld stop
Stopping mysqld:                                           [  OK  ]

修改配置文件

[root@slave ~]# vim /etc/my.cnf 
[mysqld]
server-id=2

启动从

[root@slave ~]# service mysqld start
Starting mysqld:                                           [  OK  ]

配置

sql> change master to master_host='128.0.0.101',master_user='slave',master_password='123456';
Query OK, 0 rows affected (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

启动从服务

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

查看从状态

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

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 128.0.0.101
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-master.000001
          Read_Master_Log_Pos: 330
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 482
        Relay_Master_Log_File: mysql-bin-master.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 330
              Relay_Log_Space: 638
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1 row in set (0.00 sec)

到此配置完毕

希望大家能交流技术,有问题一起研究,指出我的不足,让我进步

原文发布于微信公众号 - 从零开始的linux(gh_4a9d788f5f27)

原文发表时间:2017-10-01

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏性能与架构

Mysql 主从复制日志参数优化

MySQL的复制(Replication),实际上就是通过将Master端的Binlog利用IO线程通过网络复制到Slave端,然后再通过SQL线程解析Binl...

3604
来自专栏乐沙弥的世界

使用Innobackupex快速搭建(修复)MySQL主从架构

    MySQL的主从搭建大家有很多种方式,传统的mysqldump方式是很多人的选择之一。但对于较大的数据库则该方式并非理想的选择。使用Xtrabackup...

511
来自专栏程序员同行者

mysql主从同步问题解决汇总

1282
来自专栏1分钟快速搭建MySQL主从数据库

1分钟快速搭建wwwhj8828com18088049999MySQL主从数据库

mysql-utilities工具集是一个集中了多种工具的合集,可以理解为是DBA的工具箱,本文介绍利用其中的mysqlreplicate工具来快速搭建MySQ...

3215
来自专栏cmazxiaoma的架构师之路

MySQL基于GTID主从复制的杂谈

先来回顾一下MySQL的二进制知识点。基于Row格式的日志可以避免MySQL主从复制中出现的主从不一致问题。在一个sql语句修改了1000条数据的情况下,基于段...

1905
来自专栏工科狗和生物喵

【Hadoop学起来】分布式Hadoop的搭建(Ubuntu 17.04)

正文之前 作为一个以后肯定要做大数据的人,至今还没玩过Java 和 Hadoop 会不会被老师打死?所以就想着,在我的国外的云主机上搭建个Hadoop ,以后在...

4064
来自专栏腾讯IVWEB团队的专栏

使用 Xposed 强制 androidwebView 开启 debug 模式

从 《远程调试 Android 设备使用入门》文章中我们可以知道在 android 4.4+可以通过在apk中使用下面的代码开启 webview 的 chrom...

6530
来自专栏数据和云

例证MySQL GTID与MariaDB GTID的不同之处

GTID是全称是Global Transaction Identifier,可简化MySQL的主从切换以及Failover。GTID用于在binlog中唯一标识...

1162
来自专栏Hadoop实操

如何实现CDH元数据库MySQL的主备

MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份,实现数据库的扩展。多个数据备份不仅可以加强数据的安全性,通过实现读写分离还能进一步提升数...

4908
来自专栏乐沙弥的世界

基于mysqldump搭建gtid主从

有关知识点参考: 配置MySQL GTID 主从复制 基于mysqldump快速搭建从库 使用mysqldump导出数据库

750

扫码关注云+社区

领取腾讯云代金券