1:安装mysql5.5数据库

规划:

主:server-1---10.64.5.167    

从:server-2---10.64.5.170

从:server-3---10.64.5.172

数据目录:/var/lib/mysql

下载mysql5.5

#wget   http://cdn.mysql.com//Downloads/MySQL-5.5/MySQL-client-5.5.46-1.el7.x86_64.rpm#wget   http://cdn.mysql.com//Downloads/MySQL-5.5/MySQL-server-5.5.46-1.el7.x86_64.rpm

安装

#rpm -ivh    MySQL-server-5.5.46-1.el7.x86_64.rpm#rpm -ivh    MySQL-client-5.5.46-1.el7.x86_64.rpm

拷贝配置文件

#cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

自启动:

#systemctl enable mysqld

2:配置mysql主库

主:server-1 10.64.5.167        

 

(1)配置创建需要同步的数据库cattle。

#mysqlmysql>CREATEDATABASE 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';

(2)创建用户

mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO slave1@'10.64.5.170' IDENTIFIED BY '123456';mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO slave2@'10.64.5.172' IDENTIFIED BY '123456';mysql> flush  privileges;

(3)配置文件

#vim /etc/my.cnf     添加server-id = 1log-bin=mysql-binlog-slave-updatesbinlog-do-db=cattlebinlog-ignore-db=mysql

重启mysql

(4)锁主库表

mysql> FLUSH TABLES WITH READ LOCK;

(4)显示主库信息

mysql> SHOW MASTER STATUS;

(5)另开一个终端,打包主库

#cd /var/lib/mysql#tar czvf  cattle.tar.gz cattle

(6)解锁主库表

mysql> UNLOCK TABLES;

3:配置mysql从库

从:server-2 10.64.5.170    

从:server-3 10.64.5.172

   

(1)将cattle.tar.gz 传输到slav机器

#mv cattle.tar.gz /var/lib/mysql/#cd /var/lib/mysql/#tar xf cattle.tar.gz

(2)查看修改cattle文件夹权限

#chown -R mysql:mysql cattle

(3)配置文件

#vim /etc/my.cnf------------server-2 添加server-id =2log_bin  = mysql-binrelay_log  = mysql-relay-binread-only=1replicate-do-db=cattlelog-slave-updates=1
------------server-3 添加server-id =3log_bin  = mysql-binrelay_log  = mysql-relay-binread-only=1replicate-do-db=cattlelog-slave-updates=1

重启slave的mysql

(4)验证连接

从库server-2上测试连接主库

#mysql -h10.64.5.167 -uslave1 -p123456mysql> show grants for slave1@10.64.5.170;

从库server-3上测试连接主库

# mysql -h10.64.5.167 -uslave2 -p123456mysql> show grants for slave2@10.64.5.172;

(5)设置slave复制

查询master的position值

mysql> SHOW  MASTER STATUS\G;*************************** 1. row ***************************            File: mysql-bin.000001        Position: 120    Binlog_Do_DB: cattleBinlog_Ignore_DB: mysql1 row in set (0.00 sec)ERROR: No query specified
server-2配置mysql>CHANGE MASTER TO MASTER_HOST='10.64.5.167',         -> MASTER_USER='slave1',         -> MASTER_PASSWORD='123456',         -> MASTER_LOG_FILE='mysql-bin.000001',         -> MASTER_LOG_POS=120;     -------MASTER_LOG_POS为主库的Position
server-3配置mysql>CHANGE MASTER TO MASTER_HOST='10.64.5.167',         -> MASTER_USER='slave2',         -> MASTER_PASSWORD='123456',        -> MASTER_LOG_FILE='mysql-bin.000001',         -> MASTER_LOG_POS=120;

(6)slave启动

mysql> START SLAVE;Query OK, 0 rows affected, 1 warning (0.00 sec)

运行SHOW SLAVE STATUS查看输出结果:

主要查看

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

mysql> SHOW SLAVE STATUS\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 10.64.5.167                  Master_User: slave1                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000001          Read_Master_Log_Pos: 120               Relay_Log_File: mysql-relay-bin.000002                Relay_Log_Pos: 178995708        Relay_Master_Log_File: mysql-bin.000001             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB: cattle          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: 178995562              Relay_Log_Space: 178995864              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: 0Master_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: 11 row in set (0.00 sec)

验证master

mysql> SHOW  PROCESSLIST\G;*************************** 1. row ***************************     Id: 14   User: system user   Host:      db: NULLCommand: Connect   Time: 63424  State: Connecting to master   Info: NULL*************************** 2. row ***************************     Id: 15   User: system user   Host:      db: NULLCommand: Connect   Time: 63424  State: Slave has read all relay log; waiting for the slave I/O thread to update it   Info: NULL*************************** 3. row ***************************     Id: 25   User: slave1   Host: 10.64.5.170:47139     db: NULLCommand: Binlog Dump   Time: 62967  State: Master has sent all binlog to slave; waiting for binlog to be updated   Info: NULL*************************** 4. row ***************************     Id: 244   User: slave2   Host: 10.64.5.172:45629     db: NULLCommand: Binlog Dump   Time: 53898  State: Master has sent all binlog to slave; waiting for binlog to be updated   Info: NULL16 rows in set (0.00 sec)ERROR: No query specified

4:验证主从同步

在主库server-1中创建一个表

mysql> USE cattle;Database changedmysql> CREATE TABLE `test` (`name`  varchar(10) NULL ,`old`  char(10) NULL );Query OK, 0 rows affected (0.00 sec)mysql> DESC test;     +-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| name  | varchar(10) | YES  |     | NULL    |       || old   | char(10)    | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)

从库查询是否有这个新表

mysql> USE cattle;Database changedmysql> 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)

至此,mysql的主从复制完成。