更多MySQL复制的知识请看
环境：
- 主数据库master，本地win7，192.168.1.102
- 从数据库slave，虚拟机CentOS，192.168.56.1
1. 修改主从数据库配置
修改master数据库
（my.ini的局部 ）
[mysqld] log-bin=mysql-bin #[必须]启用二进制日志 server-id=1 #[必须]服务器唯一ID，默认是1，一般取IP最后一段
# binlog-do-db=testbbc #[可选]指定需要同步的数据库
修改slave数据库
（my.cnf的局部）
log-bin=mysql-bin #[不是必须]启用二进制日志 server-id=2 #[必须]服务器唯一ID，默认是1，一般取IP最后一段 # binlog-do-db=testbbc #[可选]//同步数据库
重启主从数据库
2. 配置主从数据库
登录master数据库，给slave数据库授权
mysql> grant replication slave on *.* to 'root'@'192.168.56.1' identified by 'root'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> select host,user,password from mysql.user; +--------------+------+-------------------------------------------+ | host | user | password | +--------------+------+-------------------------------------------+ | localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | | 127.0.0.1 | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | | ::1 | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | | % | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | | 192.168.56.1 | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | +--------------+------+-------------------------------------------+ 5 rows in set (0.00 sec)
查看master数据库的状态
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 333 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) # 这里需要记录 File 以及 Position 的值，在操作从服务器时会用到
配置slave服务器
# 执行同步SQL语句 mysql> change master to master_host='192.168.1.102',master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=333; Query OK, 0 rows affected (0.06 sec) # 启动同步进程 mysql> start slave; Query OK, 0 rows affected (0.00 sec) # 主从同步检查 mysql> show slave status G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.102 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 333 Relay_Log_File: Centos6-relay-bin.000002 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.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: 333 Relay_Log_Space: 411 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) # 可以看到：Slave_IO_Running | Slave_SQL_Running两个值都是YES，说明配置成功了
3. 主从数据库测试
主数据库创建数据库，并在这个库建表，插入一条记录
mysql> create database test_db; Query OK, 1 row affected (0.00 sec) mysql> use test_db; Database changed mysql> create table test_tb(id int, name varchar(20)); Query OK, 0 rows affected (0.06 sec) mysql> insert into test_tb(id,name) values(1, 'aaaa'); Query OK, 1 row affected (0.00 sec)
分别查看主从数据库
# 主数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | test_db | | testbbc | +--------------------+ 6 rows in set (0.17 sec) mysql> select * from test_tb; +------+------+ | id | name | +------+------+ | 1 | aaaa | +------+------+ 1 row in set (0.00 sec)
# 从数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | test_db | +--------------------+ 5 rows in set (0.02 sec) mysql> use test_db; Database changed mysql> select * from test_tb; +------+------+ | id | name | +------+------+ | 1 | aaaa | +------+------+ 1 row in set (0.00 sec)
结果主从数据库都有test_db和test_tb表以及表数据，说明主从数据库配置成功！！！
my.ini配置
1 [client] 2 port=3306 3 [mysql] 4 default-character-set=utf8 5 6 7 8 9 [mysqld] 10 port=3306 11 basedir="D:/phpStudy/MySQL/" 12 datadir="D:/phpStudy/MySQL/data/" 13 character-set-server=utf8 14 default-storage-engine=INNODB 15 16 #Master Config 17 server-id=1 18 log-bin=mysql-bin 19 binlog-do-db=testbbc 20 21 22 #支持 INNODB 引擎模式。修改为 default-storage-engine=INNODB 即可。 23 #如果 INNODB 模式如果不能启动，删除data目录下ib开头的日志文件重新启动。 24 25 sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 26 max_connections=512 27 28 query_cache_size=0 29 table_cache=256 30 tmp_table_size=18M 31 32 thread_cache_size=8 33 myisam_max_sort_file_size=64G 34 myisam_sort_buffer_size=35M 35 key_buffer_size=25M 36 read_buffer_size=64K 37 read_rnd_buffer_size=256K 38 sort_buffer_size=256K 39 40 innodb_additional_mem_pool_size=2M 41 42 innodb_flush_log_at_trx_commit=1 43 innodb_log_buffer_size=1M 44 45 innodb_buffer_pool_size=47M 46 innodb_log_file_size=24M 47 innodb_thread_concurrency=8
my.cnf
1 # if all processes that need to connect to mysqld run on the same host. 2 # All interaction with mysqld must be made via Unix sockets or named pipes. 3 # Note that using this option without enabling named pipes on Windows 4 # (via the "enable-named-pipe" option) will render mysqld useless! 5 # 6 #skip-networking 7 8 # Replication Master Server (default) 9 # binary logging is required for replication 10 log-bin=mysql-bin 11 12 # binary logging format - mixed recommended 13 binlog_format=mixed 14 15 # required unique id between 1 and 2^32 - 1 16 # defaults to 1 if master-host is not set 17 # but will not function as a master if omitted 18 server-id = 2 19 binlog-do-db=testbbc 20 "/etc/my.cnf" 145L, 4717C 49,1 30% 21 # The MySQL server 22 [mysqld] 23 port = 3306 24 socket = /var/lib/mysql/mysql.sock 25 skip-external-locking 26 key_buffer_size = 16M 27 max_allowed_packet = 1M 28 table_open_cache = 64 29 sort_buffer_size = 512K 30 net_buffer_length = 8K 31 read_buffer_size = 256K 32 read_rnd_buffer_size = 512K 33 myisam_sort_buffer_size = 8M 34 35 # Don't listen on a TCP/IP port at all. This can be a security enhancement, 36 # if all processes that need to connect to mysqld run on the same host. 37 # All interaction with mysqld must be made via Unix sockets or named pipes. 38 # Note that using this option without enabling named pipes on Windows 39 # (via the "enable-named-pipe" option) will render mysqld useless! 40 # 41 #skip-networking 42 43 # Replication Master Server (default) 44 # binary logging is required for replication 45 log-bin=mysql-bin 46 47 # binary logging format - mixed recommended 48 binlog_format=mixed 49 50 # required unique id between 1 and 2^32 - 1 51 # defaults to 1 if master-host is not set 52 # but will not function as a master if omitted 53 server-id = 2 54 binlog-do-db=testbbc 55 56 # Replication Slave (comment out master section to use this) 57 # 58 # To configure this host as a replication slave, you can choose between 59 # two methods : 60 # 61 # 1) Use the CHANGE MASTER TO command (fully described in our manual) - 62 # the syntax is: 63 # 64 # CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>, 65 # MASTER_USER=<user>, MASTER_PASSWORD=<password> ; 66 # 67 # where you replace <host>, <user>, <password> by quoted strings and 68 # <port> by the master's port number (3306 by default). 69 # 70 # Example: 71 # 72 # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, 73 # MASTER_USER='joe', MASTER_PASSWORD='secret'; 74 # 75 # OR 76 # 77 # 2) Set the variables below. However, in case you choose this method, then 78 # start replication for the first time (even unsuccessfully, for example 79 # if you mistyped the password in master-password and the slave fails to 80 # connect), the slave will create a master.info file, and any later 81 # change in this file to the variables' values below will be ignored and 82 # overridden by the content of the master.info file, unless you shutdown 83 # the slave server, delete master.info and restart the slaver server. 84 # For that reason, you may want to leave the lines below untouched 85 # (commented) and instead use CHANGE MASTER TO (see above) 86 # 87 # required unique id between 2 and 2^32 - 1 88 # (and different from the master) 89 # defaults to 2 if master-host is set 90 # but will not function as a slave if omitted 91 #server-id = 2 92 # 93 # The replication master for this slave - required 94 #master-host = <hostname> 95 # 96 # The username the slave will use for authentication when connecting 97 # to the master - required 98 #master-user = <username> 99 # 100 # The password the slave will authenticate with when connecting to 101 # the master - required 102 #master-password = <password> 103 # 104 # The port the master is listening on. 105 # optional - defaults to 3306 106 #master-port = <port> 107 # 108 # binary logging - not required for slaves, but recommended 109 #log-bin=mysql-bin 110 111 # Uncomment the following if you are using InnoDB tables 112 #innodb_data_home_dir = /usr/local/mysql/data 113 #innodb_data_file_path = ibdata1:10M:autoextend 114 #innodb_log_group_home_dir = /usr/local/mysql/data 115 # You can set .._buffer_pool_size up to 50 - 80 % 116 # of RAM but beware of setting memory usage too high 117 #innodb_buffer_pool_size = 16M 118 #innodb_additional_mem_pool_size = 2M 119 # Set .._log_file_size to 25 % of buffer pool size 120 #innodb_log_file_size = 5M 121 #innodb_log_buffer_size = 8M 122 #innodb_flush_log_at_trx_commit = 1 123 #innodb_lock_wait_timeout = 50 124 125 [mysqldump] 126 quick 127 max_allowed_packet = 16M 128 129 [mysql] 130 no-auto-rehash 131 # Remove the next comment character if you are not familiar with SQL 132 #safe-updates 133 134 [myisamchk] 135 key_buffer_size = 20M 136 sort_buffer_size = 20M 137 read_buffer = 2M 138 write_buffer = 2M 139 140 [mysqlhotcopy] 141 interactive-timeout