mysql的主从复制
由于mysql的架构中少不了主从，所以今天在虚拟机上做了一遍；两台mysql服务器主服务器 192.168.1.184从服务器 192.168.1.233主从复制实现的功能： 主服务器中的数据同步到从服务器上，保证两台数据库中的数据实时同步；1、主服务器的配置 1.1、安装mysql
# yum -y install mysql mysql-server 1.2、启动mysql
#service mysqld start 1.3、设置mysql的密码
#mysqladmin -uroot password 123456(由于是虚拟机，密码简单点，生产环境切勿设置此密码) 1.4、修改mysql的配置文件
#vim /etc/my.conf ---在标签下添加一下几行 log-bin=mysql-bin server-id=184 ---一般设置为服务器ip的最后一段 1.5、重新启动mysql
#service mysqld restart 1.6、在主服务器上建立账户并授权slave #mysql -uroot -p 123456 mysql>grant replication slaveon *.*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 specified2、从服务器配置 2.1、安装mysql
# yum -y install mysql mysql-server 2.2、启动mysql
#service mysqld start 2.3、设置mysql的密码
#mysqladmin -uroot password 123456 2.4、修改mysql的配置文件
#vim /etc/my.conf ---在标签下添加 server-id=233 ---一般设置为服务器ip的最后一段 2.5、重新启动mysql
#service mysqld restart 2.6、进入mysql #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，只出现一个或者一个没有，请看看你的操作，另外，记得把防火墙关了serviceiptables 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)
至此，主从复制算是成功了，希望对你有所帮助
页:
[1]