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)到此配置完毕
希望大家能交流技术，有问题一起研究，指出我的不足，让我进步
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2017-10-01，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。