☰

mysql 复制设置但不工作

tag-icon • tag-icon mysql-replication
mysql 复制设置但不工作

我在主服务器和从服务器之间设置 mysql 复制时遇到了一些麻烦。

我已成功设置，但数据没有更新。

Master ：显示主控状态；

[File]: mysql-bin.000033
[Position]: 1757196
[Binlog_Do_DB]: ciel

Master ：显示进程列表；

[User]: slave
[Host]: 92.222.177.xxx:57578 ( right slave ip )
[db]: 
[Command]: Binlog Dump
[Time]: 1231
[State]: Has sent all binlog to slave; waiting for binlog to be updated

从属：显示从属状态；

[Slave_IO_State]: Waiting for master to send event
[Master_Host]: 46.105.122.xxx
[Master_User]: slave
[Master_Port]: 3306
[Connect_Retry]: 60
[Master_Log_File]: mysql-bin.000033
[Read_Master_Log_Pos]: 1757196
[Relay_Log_File]: mysqld-relay-bin.000006
[Relay_Log_Pos]: 252
[Relay_Master_Log_File]: mysql-bin.000033
[Slave_IO_Running]: Yes
[Slave_SQL_Running]: Yes
[Replicate_Do_DB]: ciel
[Exec_Master_Log_Pos]: 1757196
[Relay_Log_Space]: 409
[Until_Condition]: None
[Master_SSL_Allowed]: No
[Master_SSL_Verify_Server_Cert]: No
[Master_Server_Id]: 1

从属：显示进程列表；

[User]: system user
[Host]: 
[db]: 
[Command]: Connect
[Time]: 1231
[State]: Waiting for master to send event
[Info]: 

[Id]: 2
[User]: system user
[Host]: 
[db]: 
[Command]: Connect
[Time]: 1231
[State]: Slave has read all relay log; waiting for the slave I/O thread to update it

然后选择主数据：

master: lastmod: 2014-10-26 17:14:55
slave:  lastmod: 2014-10-26 15:45:45

我感觉很迷茫，因为 8 个小时过去了我仍然没有找到如何正确设置它。

这是 masters my.cnf

[client]
port          = 3306
socket          = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice          = 0
[mysqld]
wait_timeout=24000
interactive_timeout=24000

server-id=1
log-bin=/var/log/mysql/mysql-bin.log
expire_logs_days  = 10
max_binlog_size   = 100M
binlog-do-db=ciel

max_connections=0
tmp_table_size=52M
max_heap_table_size=52M
table_cache = 1000
thread_cache_size = 12
query_cache_limit=2M
query_cache_size = 62M
query_cache_min_res_unit=220

long_query_time=3
log_error=/l/mysqlerr.log
log_slow_queries=/l/mysqlslow.log
#bind-address=0.0.0.0
user          = mysql
pid-file     = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port          = 3306
basedir          = /usr
datadir          = /var/lib/mysql
tmpdir          = /tmp
language     = /usr/share/mysql/english
skip-external-locking
key_buffer =80M
max_allowed_packet = 80M
myisam-recover         = BACKUP
innodb_file_per_table = 1
thread_concurrency = 8
myisam_sort_buffer_size = 64M
read_rnd_buffer_size = 8M
read_buffer_size = 2M
sort_buffer_size = 2M

[mysqldump]
quick
quote-names
max_allowed_packet=128M
[mysql]
[isamchk]
key_buffer = 256M
write_buffer = 2M
read_buffer = 2M
sort_buffer_size = 256M
!includedir /etc/mysql/conf.d/

然后从属 cnf

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0
[mysqld]
log_bin     = /var/log/mysql/mysql-bin.log
server-id=2
log-slave-updates = 1
replicate-do-db='ciel'
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir     = /usr
datadir = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
key_buffer      = 16M
max_allowed_packet = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit   = 1M
query_cache_size = 16M
expire_logs_days    = 10
max_binlog_size         = 100M
innodb_file_per_table = 1
default-storage-engine = InnoDB
[mysqldump]
quick
quote-names
max_allowed_packet  = 16M
[mysql]
[isamchk]
key_buffer      = 16M
!includedir /etc/mysql/conf.d/

答案1

replicate-do-db='ciel'

删除引号。您的复制系统运行正常，但却因为这个原因而无法执行任何操作。

答案2

我当然知道问题出在哪里。

设置正确，但 php 代码中的所有查询都使用以下格式：

update database.table

所以两个 my.cnf 都需要该参数。

binlog-format=row;

相关内容