MySQL notes--small try MySQL master-slave configuration

MySQL master configuration:

Hardware: Two servers 1, Ubuntu 12.04.4 lts (gnu/linux 3.2.0-60-generic-pae i686)  2, Ubuntu 12.04.4 lts (Gnu/linux 3.2.0-60- Generic-pae i686) Install mysqmysql Ver 14.14 distrib 5.5.35 respectively, for DEBIAN-LINUX-GNU (i686) using ReadLine 6.2  master server (192.168 .1.101,root,root) from server (192.168.1.102,root,root)   Primary server configuration: Open the profile of the master server/etc/mysql/my.cnf find [mysqld] Configuration block Change the configuration entry: Server-id = 1                           &NBSP ;                                 #主机标示, integer log _bin                 =    /var/log/mysql/mysql-bin.log     #确保此 File writable binlog-do-db         =    zhu_db                                   #需要备份数据库, multiple write multiline #binlog-ignore-db  =    mysql                 &nbsp                   #不需要备份的数据库, multiple write multiple lines  skip-external-locking and bind-address Option to comment out or you cannot connect to the database remotely. WS-Save restart MySQL. Create a user from the server on the primary server: Grant replication Slave on * * to ' slave01 ' @ ' 192.168.1.102  ' identified by ' slave01 ' ; #grant replication Slave on * * to ' username ' @ ' from server address ' identified by ' password '; First *: Indicates all databases Second *: Indicates all tables # from the address of the service can also write address segments such as ... ' Slave01 ' @ ' 192.% ' ... After the account is established, the Permission Association table is refreshed by the command: flush privileges; You can also view the user table of the MySQL database with a message about SLAVE01 users ' permissions   see the status of Master, followed by file   Two parameters of position mysql> Show master status;+------------------+----------+--------------+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+| mysql-bin.000013 | 3680 | zhu_db | |+------------------+----------+--------------+------------------+1 row in Set (0.00 sec)mysql>From the server configuration: from the primary server configuration file/etc/mysql/my.cnf Find [mysqld] configuration block Change the configuration entry: Server-id = 2                                  #主机标示, Integer log_bin =/var/log/mysql/mysql-bin.log #确保此文件可写binlog-do-db = zhu_db #需要备份数据库, multiple write multiple lines #binlog-ignore-db = MySQL #不需要备份的数据库, multiple write MultiRow Note: After MySQL 5.1.7, it is not supported to write the following configuration attributes of Master to the MY.CNF configuration file, just write the synchronized database and the database to be ignored. Master_host = 192.168.196.68master_port = 3306master_user = Slavemaster_password = 111321master_connect_retry = Ten #如果从服务 The device discovers that the primary server is disconnected, and the time difference (in seconds) of reconnection is the same as master, canceling the remote connection limit (no cancellation should be no problem, never tried). Mysql-u root-p #进入MySQL控制台slave stop; #停止slave同步进程在从服务器上创建一个用户change Master to master_host= ' 10.2.6.11 ', master_user= ' slave01 ', master_password= ' slave01 ', Master_log_file= ' mysql-bin.000013 ', master_log_pos=3680; #执行同步语句 #change Master to master_host= ' home server address ', master_user= ' master server ' created username ', master_password= ' corresponding password ', master_log_file= ' master server Binlog file ', master_log_pos= start synchronizing location; slave start; #开启slave同步进程查看从服务器的状态mysql> show Slave status\g*************************** 1. Row ***************************slave_io_state:waiting for Master to send eventmaster_host:10.2.6.11master_user:slave01master_port:3306connect_retry:60master_log_file:mysql-bin.000013read_master_log_pos:3680relay_log_file:mysqld-relay-bin.000004relay_log_pos:748relay_master_log_file:mysql-bin.000013Slave_io_running:yesSlave_sql_running:yesreplicate_do_db:replicate_ignore_db:replicate_do_table:replicate_ignore_table:replicate_wild_do_table:replicate_wild_ignore_table:last_errno:0Last_error:skip_counter:0exec_master_log_pos:3680relay_log_space:905Until_condition:noneUntil_log_file:until_log_pos:0Master_ssl_allowed:noMaster_ssl_ca_file:Master_ssl_ca_path:Master_ssl_cert:Master_ssl_cipher:Master_ssl_key:seconds_behind_master:0Master_ssl_verify_server_cert:nolast_io_errno:0Last_io_error:last_sql_errno:0Last_sql_error:Replicate_ignore_server_ids:master_server_id:11 row in Set (0.00 sec)mysql>Note check:Slave_io_running:yesSlave_sql_running:yesThe values for these two parameters are yes, which means the configuration was successful! Testing at this point you can create a database, data tables, and insert data on the primary server to see if updates can be synchronized from the server.

MySQL notes--small try MySQL master-slave configuration