发布于 2015-11-14 15:24:35 | 594 次阅读 | 评论: 0 | 来源: PHPERZ
Mysql关系型数据库管理系统
MySQL是一个开放源码的小型关联式数据库管理系统，开发者为瑞典MySQL AB公司。MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。
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。
#mysql mysql>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 = 1 log-bin=mysql-bin log-slave-updatesbinlog-do-db=cattle binlog-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 =2 log_bin = mysql-bin relay_log = mysql-relay-bin read-only=1 replicate-do-db=cattle log-slave-updates=1
------------server-3 添加 server-id =3 log_bin = mysql-bin relay_log = mysql-relay-bin read-only=1 replicate-do-db=cattle log-slave-updates=1
重启slave的mysql
（4）验证连接
从库server-2上测试连接主库
#mysql -h10.64.5.167 -uslave1 -p123456 mysql> show grants for slave1@10.64.5.170；
从库server-3上测试连接主库
# mysql -h10.64.5.167 -uslave2 -p123456 mysql> 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: cattle Binlog_Ignore_DB: mysql 1 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: 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)
验证master
mysql> SHOW PROCESSLIST\G; *************************** 1. row *************************** Id: 14 User: system user Host: db: NULL Command: Connect Time: 63424 State: Connecting to master Info: NULL *************************** 2. row *************************** Id: 15 User: system user Host: db: NULL Command: 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: NULL Command: 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: NULL Command: Binlog Dump Time: 53898 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL 16 rows in set (0.00 sec) ERROR: No query specified
4：验证主从同步
在主库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; +-------+-------------+------+-----+---------+-------+ | 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 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)
至此，mysql的主从复制完成。