两台MySQL数据库服务器MasterslaveMaster为主服务器,slave为从服务器,初始状态时,Masterslave中的数据信息相同,当Master中的数据发生变化时,slave也跟着发生相应的变化,使得masterslave的数据信息同步,达到备份的目的。

要点:

  负责在主、从服务器传输各种修改动作的媒介是主服务器的二进制变更日志,这个日志记载着需要传输给从服务器的各种修改动作。因此,主服务器必须激活二进制日志功能。从服务器必须具备足以让它连接主服务器并请求主服务器把二进制变更日志传输给它的权限。


实验环境:

master:192.168.127.120  ---主

slave:192.168.127.121  ---备


【master192.168.127.120 配置】

my.cnf{

symbolic-links=0

server-id=1

slave-skip-errors = all

auto_increment_increment = 2

auto_increment_offset = 2

default-storage-engine = INNODB

event_scheduler = ON

skip-external-locking

local_infile=0


master-host=192.168.127.121

master-user =slave

master-password=radiusslave

master-port=3306

log-bin = mysql-bin


binlog_format = mixed

expire_logs_days = 7

relay-log=mysqld-relay-bin

innodb_buffer_pool_size = 2G

innodb_additional_mem_pool_size = 32M

innodb_log_file_size = 256M

innodb_log_buffer_size = 16M

innodb_flush_log_at_trx_commit = 0

innodb_lock_wait_timeout = 20


innodb_thread_concurrency = 16

innodb_flush_method = O_DIRECT

innodb_max_dirty_pages_pct = 90

query_cache_type = 0

query_cache_size = 256M

lower_case_table_names=1

skip-name-resolve 


}


【master192.168.127.121 配置】

my.cnf{

symbolic-links=0

####    add


server-id=2

slave-skip-errors = all

auto_increment_increment = 2

auto_increment_offset = 2

default-storage-engine = INNODB

event_scheduler = ON

skip-external-locking

local_infile=0


#master-host=192.168.127.120

#master-user =slave

#master-password=radiusslave

#master-port=3306

log-bin = mysql-bin



binlog_format = mixed

expire_logs_days = 7

relay-log=mysqld-relay-bin

innodb_buffer_pool_size = 2G

innodb_additional_mem_pool_size = 32M

innodb_log_file_size = 256M

innodb_log_buffer_size = 16M

innodb_flush_log_at_trx_commit = 0

innodb_lock_wait_timeout = 20


innodb_thread_concurrency = 16

innodb_flush_method = O_DIRECT

innodb_max_dirty_pages_pct = 90

query_cache_type = 0

query_cache_size = 256M

lower_case_table_names=1

skip-name-resolve 

}


【master192.168.127.120 配置】


mysql> CREATE USER 'slave'@'%' IDENTIFIED BY 'slave123';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'slave'@'localhost' IDENTIFIED BY "slave123";

mysql> GRANT ALL PRIVILEGES ON *.* TO 'slave'@'%' IDENTIFIED BY "slave123";

mysql> flush privileges;

mysql>quit

mysql>show grants for 'slave'@'%';   


-----查询master 库中存在的 二进制日志文件号. 数据同步使用该日志文件来实现。

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 |      106 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)


mysql> show binary logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000001 |       106 |

+------------------+-----------+

1 row in set (0.00 sec)


mysql> 


【slave:192.168.127.121】配置主


mysql> stop  slave;

mysql> change master to master_host='192.168.127.120',master_user='slave',master_password='slave123',master_log_file='mysql-bin.000001',master_log_pos=106;

5.6版本:master_port=38306

show slave status\G; 查看配置结果


mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.127.120

                  Master_User: slave

                  Master_Port: 3306

                Connect_Retry: 10

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 106

               Relay_Log_File: mysqld-relay-bin.000002

                Relay_Log_Pos: 251

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: mysql

          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: 106

              Relay_Log_Space: 407

              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: 

1 row in set (0.00 sec)


ERROR: 

No query specified


mysql> 

     Slave_IO_Running: Yes

     Slave_SQL_Running: Yes         此2项显示则配置成功



【slave 192.168.127.121 配置】

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY 'slave123';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'slave'@'localhost' IDENTIFIED BY "slave123"

mysql> GRANT ALL PRIVILEGES ON *.* TO 'slave'@'%' IDENTIFIED BY "slave123"

mysql> flush privileges;

mysql>quit

mysql>show grants for 'slave'@'%';   


-----查询master 库中存在的 二进制日志文件号. 数据同步使用该日志文件来实现。


mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000005 |      106 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)


mysql> show binary logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000001 |       125 |

| mysql-bin.000002 |       125 |

| mysql-bin.000003 |       125 |

| mysql-bin.000004 |       125 |

| mysql-bin.000005 |       106 |

+------------------+-----------+

5 rows in set (0.00 sec)

【master :192.168.127.121】配置主

mysql> stop  slave;

mysql> change master to master_host='192.168.15.92',master_user='slave',master_password='slave123',master_log_file='mysql-bin.000009',master_log_pos=120;

5.6版本:master_port=38306

show slave status\G; 查看配置结果



mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.127.121

                  Master_User: slave

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000005

          Read_Master_Log_Pos: 106

               Relay_Log_File: mysqld-relay-bin.000002

                Relay_Log_Pos: 251

        Relay_Master_Log_File: mysql-bin.000005

             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: 106

              Relay_Log_Space: 407

              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: 

1 row in set (0.00 sec)


ERROR: 

No query specified


mysql> 


     Slave_IO_Running: Yes

     Slave_SQL_Running: Yes            此2项显示则配置成功




  mysql>  reset slave;

  mysql>  stop slave;

  mysql>  start slave;

  刷新日志file的位置会+1

  flush logs;



在主数据库中创建nodb库,workers_info表进行测试

CREATE DATABASE nodb DEFAULT CHARACTER SET utf8;


workers_info

class_info


CREATE TABLE `class_info` (  

  id int(11),  

  workername varchar(20) NOT NULL,  

  salary int(11),  

  email  varchar(30),  

  department  varchar(30)

);


insert into class_info values(1,'jeck333','20','123@163.com','dep');

查看slave数据库,在master库中创建库,建表,插入数据,都能被同步到slave中