1.配置/usr/my.cnf文件,主要是在原来单机多实例的基础上添加“主服务添加部分”和“从服务器添加部分”,
如果不会单机配置多实例,请参考上一章。
[mysqld_multi]
mysqld=/usr/bin/mysqld_safe
mysqladmin=/usr/bin/mysqladmin
user=root
[mysqld3306]
port=3306
socket=/var/lib/mysql/mysql.sock
pid-file=/var/lib/mysql/localhost.localdomain.pid
datadir=/var/lib/mysql/
user=root
character_set_server=utf8
lower_case_table_names=1
max_connections=300
server-id=1 #主服务器添加
binlog-do-db=test #主服务器添加
log-bin=master-bin #主服务器添加
log-bin-index=master-bin.index #主服务器添加
[mysqld3307]
port=3307
socket=/home/fuyouling/mutl_mysql/mysql_3307/mysql/mysql.sock
pid-file=/home/fuyouling/mutl_mysql/mysql_3307/mysql/localhost.localdomain.pid
datadir=/home/fuyouling/mutl_mysql/mysql_3307/mysql/
user=root
character_set_server=utf8
lower_case_table_names=1
max_connections=300
server_id=2 #从服务器添加部分
replicate-do-db=test #从服务器添加部分
relay-log=slave-relay-bin #从服务器添加部分
relay-log-index=slave-relay-bin.index #从服务器添加部分
2.master主服务器重新开启mysql服务
mysqld_multi --defaults-extra-file=/usr/my.cnf start 3306 #开启3306服务
mysql -uroot -p123456 -S /var/lib/mysql/mysql.sock #登录mysql
mysql>grant replication slave,replication client on *.* to repl@'localhost' identified by 'repl'; #在主服务器上建立复制用户
mysql>flush privileges; #刷新权限
4.在slave从服务器上开启mysql服务
mysqld_multi --defaults-extra-file=/usr/my.cnf start 3307 #开启3307服务
mysql -uroot -p654321 -S /home/fuyouling/mutl_mysql/mysql_3307/mysql/mysql.sock #登录mysql
mysql>Change Master to Master_host='127.0.0.1',Master_port=3306,Master_user='repl',Master_Password='repl';#Slave连接到Master的信息
5.检查主从配置状态
在主服务器上执行:
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000004 | 120 | test | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在从服务器上执行:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000004
Read_Master_Log_Pos: 120
Relay_Log_File: slave-relay-bin.000009
Relay_Log_Pos: 284
Relay_Master_Log_File: master-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
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: 120
Relay_Log_Space: 621
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
Master_UUID: 455c7d23-3c49-11e6-9bf9-000c29ec0883
Master_Info_File: /home/fuyouling/mutl_mysql/mysql_3307/mysql/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: 0
1 row in set (0.11 sec)
6.至此,mysql主从已经搭建起来,以下做个简单的测试
在主服务器上执行:
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> create table test_table(id int,str varchar(100));
Query OK, 0 rows affected (0.07 sec)
mysql> insert into test_table select 1,'this is a test';
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
在从服务器上查看,新添加的test_table已经加进来了:
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 |
+----------------+
| hahahaha |
| test_table |
| wochenggongle |
+----------------+
3 rows in set (0.00 sec)
mysql> select * from test_table;
+------+----------------+
| id | str |
+------+----------------+
| 1 | this is a test |
+------+----------------+
1 row in set (0.09 sec)
转载于:https://blog.51cto.com/quguanhai/1794220