首页 > 代码库 > mysql的主从复制
mysql的主从复制
由于mysql的架构中少不了主从，所以今天在虚拟机上做了一遍；
两台mysql服务器
主服务器 192.168.1.184
从服务器 192.168.1.233
主从复制实现的功能：
主服务器中的数据同步到从服务器上，保证两台数据库中的数据实时同步；
1、主服务器的配置
1.1、安装mysql
[root@localhost ~]# yum -y install mysql mysql-server
1.2、启动mysql
[root@localhost ~]#service mysqld start
1.3、设置mysql的密码
[root@localhost ~]#mysqladmin -uroot password 123456(由于是虚拟机，密码简单点，生产环境切勿设置此密码)
1.4、修改mysql的配置文件
[root@localhost ~]#vim /etc/my.conf ---在[mysqld]标签下添加一下几行
log-bin=mysql-bin
server-id=184 ---一般设置为服务器ip的最后一段
1.5、重新启动mysql
[root@localhost ~]#service mysqld restart
1.6、在主服务器上建立账户并授权slave
[root@localhost ~]#mysql -uroot -p 123456
mysql>grant replication slave on *.* to ‘backup‘@‘%‘ identified by ‘123456‘;
mysql>flush privileges;
1.7、查询主服务器master的二进制日志当前状态，记录下当前日志文件名及位置，后面用的到
msyql>show master status;
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000001 | 523 | | |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)
msyql>select * from mysql.user where user = ‘backup‘ \G;
*************************** 1. row ***************************
Host: %
User: backup
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: Y ---为y时说明成功
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
1 row in set (0.07 sec)
ERROR:
No query specified
2、从服务器配置
2.1、安装mysql
[root@localhost ~]# yum -y install mysql mysql-server
2.2、启动mysql
[root@localhost ~]#service mysqld start
2.3、设置mysql的密码
[root@localhost ~]#mysqladmin -uroot password 123456
2.4、修改mysql的配置文件
[root@localhost ~]#vim /etc/my.conf ---在[mysqld]标签下添加
server-id=233 ---一般设置为服务器ip的最后一段
2.5、重新启动mysql
[root@localhost ~]#service mysqld restart
2.6、进入mysql
[root@localhost ~]#mysql -uroot -p 123456
2.7、配置从服务器
mysql>change master to
mysql>master_host = ‘192.168.1.184‘, ---主服务器ip
mysql>master_user = ‘backup‘,
mysql>master_password = ‘123456‘,
mysql>master_log_file = ‘mysql-bin.000001‘; --二进制文件名字
mysql>master_log_pos = 523; --二进制文件其实位置
2.8、开启从服务器复制二进制日志
mysql>slave start;
2.9、检查从服务器复制状态
mysql>show slave status\G; ---Slave_IO_Running: Yes 双yes说明成功
Slave_SQL_Running: Yes
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.184
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 523
Relay_Log_File: localhost-relay-bin.000010
Relay_Log_Pos:
Relay_Master_Log_File: mysql-bin.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: 612037678
Relay_Log_Space: 557
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
ERROR:
No query specified
主从复制到这就算成功了，如果最后没有出现双yes，只出现一个或者一个没有，请看看你的操作，另外，记得把防火墙关了
service iptables stop
下面来验证一下
3、验证
3.1、主服务器 3.2、从服务器
查看数据库 查看数据库
mysql> show databases; mysql>show databases;
+--------------------+ +--------------------+
| Database | | Database |
+--------------------+ +--------------------+
| information_schema | | information_schema |
| backup | | backup |
| mysql | | mysql |
| test | | performance_schema |
+--------------------+ | test |
4 rows in set (0.06 sec) +--------------------+
创建一个新的数据库 5 rows in set (0.06 sec)
mysql> create database mysqldb;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| backup |
| mysql |
| mysqldb |
| test |
+--------------------+
5 rows in set (0.00 sec)
创建成功
查看从服务器的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| backup |
| mysql |
| mysqldb |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
至此，主从复制算是成功了，希望对你有所帮助
本文出自 “12178027” 博客，请务必保留此出处http://12188027.blog.51cto.com/12178027/1863938
mysql的主从复制