CentOS 7 yum安装MySQL5.6 rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm yum -y install mysql-community-server systemctl enable mysqld systemctl start mysqld 初始化： mysql_secure_installation 一路回车 创建数据库 配置创建需要同步的数据库cattle。 #mysql mysql>CREATE DATABASE IF NOT EXISTS cattle COLLATE='utf8_general_ci' CHARACTER SET='utf8'; mysql>GRANT ALL ON cattle.*TO 'cattle'@'%' IDENTIFIED BY 'cattle'; mysql>GRANT ALL ON cattle.*TO 'cattle'@'localhost' IDENTIFIED BY 'cattle'; grant all privileges on *.* to 'test'@'%' identified by 'test' with grant option; 在主数据库创建同步命令 grant replication slave on *.* to 'slaveuser'@'%' identified by '123456'; FLUSH PRIVILEGES; 锁主库表 mysql> FLUSH TABLES WITH READ LOCK; 显示主库信息 mysql> SHOW MASTER STATUS; 另开一个终端，打包主库 cd /var/lib/mysql tar czvf cattle.tar.gz cattle 拷贝到另外机器 scp cattle.tar.gz root@192.168.177.183:/var/lib/mysql/ mysql> UNLOCK TABLES; service mysqld stop 修改/etc/my.cnf 添加主 [mysqld] server-id=130 log_bin=mysql-bin.000002 自己定义 保存 在/var/lib/mysql/会看到mysql-bin.000002.index 这个很重要 查看刚才创建的用户授权结果: 命令: 切换至mysql数据库 mysql> use mysql mysql>select * from user where host='%' and user='slaveuser' \G; *************************** 1. row *************************** Host: % User: slaveuser 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 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 Create_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: password_expired: N 1 row in set (0.00 sec) ERROR: No query specified Repl_slave_priv项为Y，表示授权成功 2.5查看master数据库状态 注意:查看之前做好数据库只读操作,防止在配置过程中出现写入数据日志，导致查询的master日志不可控 查看master数据库状态 命令: mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 6187 | | mysql | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.01 sec) mysql> 日志文件名:称 mysql-bin.000002 日志文件位置: 6187 配置从数据库slave 如果master中需要同步的数据库已经存在且有数据,则需要将master中的数据库导出到slave保持master和slave需要同步的数据库信息一致。 3.1停止slave数据库服务 命令: service mysqld stop 3.2修改/etc/my.cnf配置文件 在[mysqld]的节点下添加以下配置: server-id=133 注意ID不能重复 stop slave; 配置master信息: mysql>change master to master_host='192.168.177.182', master_user='slaveuser', master_password='123456', master_log_file='mysql-bin.000002', master_log_pos=402; 配置说明: master_host:master的主机地址 master_user:master上创建的同步数据用户,之前创建的slaveuser master_password:master创建的同步数据用户密码 master_log_file:master最后一步查看的日志文件名 master_log_pos:master最后一步查看的日志当前位置,同步将从这个点开始 start slave; mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.8.202 Master_User: slaveuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 23305 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000002 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: 23305 Relay_Log_Space: 457 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: 202 Master_UUID: 4aed5689-6c69-11e7-9b1f-000c290e4f3d Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 注意,上面两个配置 Slave_IO_Running: Yes Slave_SQL_Running: Yes Yes标识已经成功配置 验证主从同步 在主库server-1中创建一个表 mysql> USE cattle; Database changed mysql> CREATE TABLE `test` (`name` varchar(10) NULL ,`old` char(10) NULL ); Query OK, 0 rows affected (0.00 sec) mysql> DESC test; 从库查询是否有这个新表 mysql> USE cattle; Database changed mysql> DESC test; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(10) | YES | | NULL | | | old | char(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)