mysql主從複製

標籤：mysql

mysql實現主從複製

實驗環境：

server5：主

server6：從

server7：從

iptabls offselinux Disabled12345

安裝mysql

*先查看是否有其它版本的,若有，則先卸載

[[email protected] ~]# rpm -qa | grep mysqlmysql-libs-5.1.71-1.el6.x86_64

[[email protected] ~]# rpm -e mysql-libs-5.1.71-1.el6.x86_64error: Failed dependencies:

   libmysqlclient.so.16()(64bit) is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64

   libmysqlclient.so.16(libmysqlclient_16)(64bit) is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64

   mysql-libs is needed by (installed) postfix-2:2.6.6-2.2.el6_1.x86_64

[[email protected] ~]# rpm -e --nodeps mysql-libs-5.1.71-1.el6.x86_64[[email protected] ~]# rpm -qa | grep mysql[[email protected] ~]# 1234567891011

*安裝mysql

[[email protected] ~]# tar zxf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar gzip: stdin: not in gzip formattar: Child returned status 1tar: Error is not recoverable: exiting now

[[email protected] ~]# tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar #注意順序[[email protected] ~]# rpm -ivh mysql-community-common-5.7.17-1.el6.x86_64.rpm[[email protected] ~]# rpm -ivh mysql-community-libs-5.7.17-1.el6.x86_64.rpm [[email protected] ~]# rpm -ivh mysql-community-devel-5.7.17-1.el6.x86_64.rpm[[email protected] ~]# yum install libaio -y[[email protected] ~]# yum install numactl -y[[email protected] ~]# rpm -ivh mysql-community-client-5.7.17-1.el6.x86_64.rpm[[email protected] ~]# rpm -ivh mysql-community-server-5.7.17-1.el6.x86_64.rpmwarning: mysql-community-server-5.7.17-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEYerror: Failed dependencies:

   /usr/bin/perl is needed by mysql-community-server-5.7.17-1.el6.x86_64

   perl(File::Path) is needed by mysql-community-server-5.7.17-1.el6.x86_64

   perl(Getopt::Long) is needed by mysql-community-server-5.7.17-1.el6.x86_64

   perl(POSIX) is needed by mysql-community-server-5.7.17-1.el6.x86_64

   perl(strict) is needed by mysql-community-server-5.7.17-1.el6.x86_64

[[email protected] ~]# yum install perl  -y[[email protected] ~]# rpm -ivh mysql-community-server-5.7.17-1.el6.x86_64.rpm[[email protected] ~]# 123456789101112131415161718192021222324

*在server5：master

[[email protected] ~]# vim /etc/my.cnf.....

log-bin=mysql-bin

binlog-do-db=test

server-id=1binlog-ignore-db=mysql

.....

[[email protected] ~]# /etc/init.d/mysqld startInitializing MySQL database:                               [  OK  ]

Installing validate password plugin:                       [  OK  ]

Starting mysqld:                                           [  OK  ]

[[email protected] ~]#[[email protected] ~]# cat /var/log/mysqld.log | grep pass  #過濾初始密碼2017-06-13T01:22:16.400573Z 1 [Note] A temporary password is generated for [email protected]: M.uepxaHq3oI

[[email protected] ~]# mysql_secure_installation        #修改mysql預設root密碼[[email protected] ~]# mysql -uroot -pXiamin+0099mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO [email protected]‘172.25.66.7‘

   -> IDENTIFIED BY ‘Xiamin+0099‘;         #建立賬戶並給與許可權Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> Flush privileges;

Query OK, 0 rows affected (0.12 sec)

mysql> quit

Bye

[[email protected] ~]#12345678910111213141516171819202122232425262728

*在server7：slave

[[email protected] ~]# vim /etc/my.cnf.....

server-id=2

.....[[email protected] ~]# /etc/init.d/mysqld start

Initializing MySQL database:                               [  OK  ]

Installing validate password plugin:                       [  OK  ]

Starting mysqld:                                           [  OK  ]

[[email protected] ~]#

[[email protected] ~]# mysql_secure_installation

[[email protected] ~]# mysql -uroot -pXiamin+0099

(此時需要先stop slave)

mysql> change master to master_host=‘172.25.66.5‘, master_user=‘westos‘,    -> master_password=‘Xiamin+0099‘, master_log_file=‘mysql-bin.000005‘, master_log_pos=616;Query OK, 0 rows affected, 2 warnings (0.50 sec)

mysql> start slave;

Query OK, 0 rows affected (0.06 sec)

mysql> show slave status\G;.....

           Slave_IO_Running: Yes

           Slave_SQL_Running: Yes

.....mysql> quit

Bye

[[email protected] ~]# 12345678910111213141516171819202122232425262728

測試：

在master中建立test庫

[[email protected] ~]# mysql -uroot -pXiamin+0099.....

mysql> show databases;

+--------------------+| Database           |

+--------------------+| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+4 rows in set (0.07 sec)

mysql> create database test;

Query OK, 1 row affected (0.08 sec)

mysql> show databases;

+--------------------+| Database           |

+--------------------+| information_schema |

| mysql              |

| performance_schema |

| sys                |

| test               |

+--------------------+5 rows in set (0.00 sec)

mysql> use test

Database changed

mysql> create table MyClass(

   -> id int(4) not null primary key auto_increment,

   -> name char(20) not null,

   -> sex int(4) not null default ‘0‘,

   -> degree double(16,2));

Query OK, 0 rows affected (1.20 sec)

mysql> show tables;

+----------------+| Tables_in_test |

+----------------+| MyClass        |

+----------------+1 row in set (0.00 sec)

mysql> show processlist;

+----+--------+---------------+------+-------------+-------+---------------------------------------------------------------+------------------+| Id | User   | Host          | db   | Command     | Time  | State                                                         | Info             |

+----+--------+---------------+------+-------------+-------+---------------------------------------------------------------+------------------+|  3 | westos | server7:50403 | NULL | Binlog Dump | 17034 | Master has sent all binlog to slave; waiting for more updates | NULL             |

| 14 | root   | localhost     | NULL | Query       |     0 | starting                                                      | show processlist |

+----+--------+---------------+------+-------------+-------+---------------------------------------------------------------+------------------+2 rows in set (0.00 sec)

mysql> quit

Bye

.....

[[email protected] ~]# 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859

在slave上查看

[[email protected] ~]# mysql -uroot -pXiamin+0099.....

mysql> show databases;

+--------------------+| Database           |

+--------------------+| information_schema |

| mysql              |

| performance_schema |

| sys                |

| test               |

+--------------------+5 rows in set (0.00 sec)

mysql> use test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+----------------+| Tables_in_test |

+----------------+| MyClass        |

+----------------+1 row in set (0.00 sec)

mysql> show processlist;

+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+| Id | User        | Host      | db   | Command | Time  | State                                                  | Info             |

+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+|  1 | system user |           | NULL | Connect |  1894 | Slave has read all relay log; waiting for more updates | NULL             |

|  2 | system user |           | NULL | Connect | 16880 | Waiting for master to send event                       | NULL             |

| 10 | root        | localhost | NULL | Query   |     0 | starting                                               | show processlist |

+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+3 rows in set (0.00 sec)

mysql> quit

Bye

.....

[[email protected] ~]# 

mysql主從複製