vlambda博客
学习文章列表

[root@localhost ~]# systemctl stop firewalld[root@localhost ~]# systemctl disable firewalld

[root@localhost ~]# getenforce  #查看selinux状态Disabled  

#192.168.3.9mysql> select version();+-----------+| version() |+-----------+| 5.6.26 |+-----------+1 row in set (0.00 sec)
mysql> # 192.168.3.13mysql> select version();+-----------+| version() |+-----------+| 5.6.26 |+-----------+1 row in set (0.00 sec)
mysql>

[root@localhost ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 29Server version: 5.6.26 Source distribution
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database Test;Query OK, 1 row affected (0.00 sec)
mysql> use Test;Database changedmysql> create table student(id int,name varchar(20));Query OK, 0 rows affected (0.01 sec)
mysql> exitBye[root@localhost ~]# systemctl stop mysqld

[root@localhost ~]# vim /etc/my.cnf log_bin=mysql-bin-master server_id = 1  #本机数据库ID标识 binlog-do-db=Test #可以被从服务器复制的库 需要同步的数据库 binlog-ignore-db=mysql #不可被从服务器复制的库[root@localhost ~]# systemctl restart mysqld

mysql> grant replication slave on *.* to slave@192.168.3.13 identified by "123456";Query OK, 0 rows affected (0.05 sec)mysql> show master status;+-------------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-------------------------+----------+--------------+------------------+-------------------+| mysql-bin-master.000001 | 330 | Test | mysql | |+-------------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)
mysql> 

[root@localhost ~]# ls /usr/local/mysql/data/auto.cnf ib_logfile0 localhost.localdomain.err mysql mysql-bin-master.index Test zabbixibdata1   ib_logfile1  localhost.localdomain.pid  mysql-bin-master.000001  performance_schema      tomcatmysql> show binlog events \G*************************** 1. row *************************** Log_name: mysql-bin-master.000001 Pos: 4 Event_type: Format_desc Server_id: 1End_log_pos: 120 Info: Server ver: 5.6.26-log, Binlog ver: 4*************************** 2. row *************************** Log_name: mysql-bin-master.000001 Pos: 120 Event_type: Query Server_id: 1End_log_pos: 330 Info: GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.3.13' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'2 rows in set (0.00 sec)
mysql> 

[root@localhost ~]# mysql -uslave -p123456 -h 192.168.3.9Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 22Server version: 5.6.26-log Source distribution
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;   #看不到Test库因为没有权限只有复制权限+--------------------+| Database |+--------------------+| information_schema |+--------------------+1 row in set (0.01 sec)
mysql> 

[root@localhost ~]# mysqldump -uroot -p Test > Test.sqlEnter password: [root@localhost ~]# scp Test.sql 192.168.3.13:/rootThe authenticity of host '192.168.3.13 (192.168.3.13)' can't be established.ECDSA key fingerprint is SHA256:SlBTEV7Ah9xBvCgAgRCxXH8QJ8VB3xNJkTL1V0aGGPs.ECDSA key fingerprint is MD5:39:ce:6b:97:0d:d1:b7:97:a1:7a:18:5c:4a:71:63:d1.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added '192.168.3.13' (ECDSA) to the list of known hosts.[email protected]'s password: Test.sql 100% 1802 681.8KB/s 00:00 [root@localhost ~]#              

[root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.6.26 Source distribution
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database Test;Query OK, 1 row affected (0.00 sec)
mysql> exitBye[root@localhost ~]# mysql -uroot -p Test<Test.sql Enter password: [root@localhost ~]# 

[root@localhost ~]# systemctl stop mysqld[root@localhost ~]# vim /etc/my.cnfserver-id = 2[root@localhost ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.26 Source distribution
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> stop slave;Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.3.9',master_user='slave',master_password='123456';Query OK, 0 rows affected, 2 warnings (0.07 sec)
mysql> start slave;Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.3.9 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin-master.000001 Read_Master_Log_Pos: 330 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 500 Relay_Master_Log_File: mysql-bin-master.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: 330 Relay_Log_Space: 677 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: 1 Master_UUID: a231a4bc-9734-11ea-822d-000c294f7b3b Master_Info_File: /usr/local/mysql/data/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: 01 row in set (0.00 sec)
#    Slave_IO_Running: Yes  看到这两行为yes则为成功     Slave_SQL_Running: Yes

mysql> use Test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A
Database changedmysql> show tables;+----------------+| Tables_in_Test |+----------------+| student |+----------------+1 row in set (0.00 sec)
mysql> insert into student values(1,'wy');Query OK, 1 row affected (0.01 sec)
mysql> select * from student;+------+------+| id | name |+------+------+| 1 | wy |+------+------+1 row in set (0.00 sec)
mysql>

[root@localhost ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.26 Source distribution
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || Test || mysql || performance_schema |+--------------------+4 rows in set (0.03 sec)
mysql> use Test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A
Database changedmysql> show tables;+----------------+| Tables_in_Test |+----------------+| student |+----------------+1 row in set (0.00 sec)
mysql> select * from student;+------+------+| id | name |+------+------+| 1 | wy |+------+------+1 row in set (0.00 sec)
mysql>