MySQL AB复制
dba
mysql
~에 대한
복사
설명하다
关于MySQL AB复制 本文讲解如何快速打包和安装MySQL， MySQL AB复制，MySQL AB双向复制，MySQL多级主从复制，解决AB双向复制主键冲突。 首先我们先介绍什么是MySQL AB复制。 AB复制又称主从复制，实现的是数据同步。如果要做MySQL AB复制，数据库版本尽量保
关于MySQL AB复制
本文讲解如何快速打包和安装MySQL， MySQL AB复制，MySQL AB双向复制，MySQL多级主从复制，解决AB双向复制主键冲突。
首先我们先介绍什么是MySQL AB复制。
AB复制又称主从复制，实现的是数据同步。如果要做MySQL AB复制，数据库版本尽量保持一致。如果版本不一致，从服务器版本高于主服务器，但是版本不一致不能做双向复制。MySQL AB复制有什么好处呢？有两点，第一是解决宕机带来的数据不一致，因为MySQL AB复制可以实时备份数据；第二点是减轻数据库服务器压力，这点很容易想到，多台服务器的性能一般比单台要好。但是MySQL AB复制不适用于大数据量，如果是大数据环境，推荐使用集群。
然后我们来看看MySQL复制的 3 个主要步骤：
1)主服务器把数据更改记录到二进制日志中，这个操作叫做二进制日志事件；
2)从服务器把主服务器的二进制日志事件拷贝到自己的中继日志（relay log）中；
3)从服务器执行中继日志中的事件，把更改应用到自己的数据上。
快速打包和安装MySQL
在正式介绍MySQL AB复制之前，介绍怎样打包MySQL和快速安装MySQL。
第二步，打包[root@serv08 ~]# find /usr/local/mysql/ /etc/my.cnf /etc/init.d/mysqld > mysql로그인 후 복사
第三步，拷贝文件到实体机[root@serv08 ~]# tar -cPvzf mysql-5.5.29-linux2.6-x86_64.tar.gz -T mysql [root@serv08 ~]# ll -h total 202M -rw-r--r--. 1 root root 411K Oct 5 19:19 mysql -rw-r--r--. 1 root root 202M Oct 5 19:21 mysql-5.5.29-linux2.6-x86_64.tar.gz로그인 후 복사
第四步，拷贝文件到serv01[root@serv08 mysql]# scp mysql-5.5.29-linux2.6-x86_64.tar.gz 192.168.1.1:/home/Wentasy/software/로그인 후 복사
第五步，解压[root@serv01 ~]# yum install /usr/bin/scp -y [root@larrywen 1005]# scp /home/Wentasy/software/mysql-5.5.29-linux2.6-x86_64.tar.gz 192.168.1.11:/opt root@192.168.1.11's password: mysql-5.5.29-linux2.6-x86_64.tar.gz 100% 201MB 33.5MB/s 00:06로그인 후 복사
第六步，创建组和用户，注意编号和安装好数据库的机器上的用户一致[root@serv01 opt]# tar -xPvf mysql-5.5.29-linux2.6-x86_64.tar.gz로그인 후 복사
第七步，改变MySQL安装目录的拥有者和所属组[root@serv01 opt]# groupadd -g 500 mysql [root@serv01 opt]# useradd -u 500 -g 500 -r -M -s /sbin/nologin mysql [root@serv01 opt]# id mysql uid=500(mysql) gid=500(mysql) groups=500(mysql)로그인 후 복사
第八步，启动MySQL，做测试[root@serv01 opt]# chown mysql.mysql /usr/local/mysql/ -R로그인 후 복사
[root@serv01 opt]# /etc/init.d/mysqld start Starting MySQL.. SUCCESS! [root@serv01 opt]# mysql -bash: mysql: command not found [root@serv01 opt]# vim ~/.bash_profile [root@serv01 opt]# . !$ . ~/.bash_profile [root@serv01 opt]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.29-log Source distribution Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>로그인 후 복사
MySQL AB单向复制
好了，相信读者已经学会怎样打包MySQL和快速安装MySQL，接下来正式进入主题，我们先来看看一主多从架构的拓扑图：
图一 一主多从架构
该图展示了一个 master 复制多个 slave 的架构，多个 slave 和单个 slave 的实施并没有实质性的区别，在 master 端并不在乎有多少个 slave 连接自己，只要有 slave 的 IO 线程通过了连接认证，向他请求指定位置之后的 binary log 信息，他就会按照该 IO 线程的要球，读取自己的binary log 信息，返回给 slave的 IO 线程。
既然对拓扑图和原理有所了解，我们做一个实验，介绍如何使用MySQL AB复制：
实验环境介绍
主机 IP地址 主机名 备注
serv01： 192.168.1.11 serv01.host.com master
serv08： 192.168.1.18 serv08.host.com slave01
操作系统版本：rhel server 6.1
所需要的软件包：mysql-5.5.29-linux2.6-x86_64.tar.gz
第一步，主服务器创建用户并清空日志
第二步，修改从服务器的server-idmysql> show privileges; mysql> grant replication client, replication slave on *.* to 'larry'@'192.168.1.%' identified by 'larry'; Query OK, 0 rows affected (0.00 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 27320 | | mysql-bin.000002 | 1035309 | | mysql-bin.000003 | 126 | | mysql-bin.000004 | 279 | +------------------+-----------+ 4 rows in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.01 sec) mysql> reset master; Query OK, 0 rows affected (0.02 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 107 | +------------------+-----------+ 1 row in set (0.00 sec)로그인 후 복사
第三步，从服务器清空日志<span>[root@serv08 ~]# cat /etc/my.cnf | grep server-id server-id = 1 #server-id = 2 [root@serv08 ~]# vim /etc/my.cnf [root@serv08 ~]# cat /etc/my.cnf | grep server-id server-id = 2 #server-id = 2 [root@serv08 ~]# /etc/init.d/mysqld restart Shutting down MySQL... SUCCESS! Starting MySQL.. SUCCESS! </span><span>#可以查看从服务器中的数据文件</span><span> [root@serv08 ~]# cd /usr/local/mysql/data/ [root@serv08 data]# ll total 29752 -rw-rw----. 1 mysql mysql 18874368 Oct 5 19:45 ibdata1 -rw-rw----. 1 mysql mysql 5242880 Oct 5 19:45 ib_logfile0 -rw-rw----. 1 mysql mysql 5242880 Oct 5 18:16 ib_logfile1 drwxr-xr-x. 2 mysql mysql 4096 Oct 5 18:15 mysql -rw-rw----. 1 mysql mysql 27320 Oct 5 18:15 mysql-bin.000001 -rw-rw----. 1 mysql mysql 1035309 Oct 5 18:15 mysql-bin.000002 -rw-rw----. 1 mysql mysql 126 Oct 5 18:16 mysql-bin.000003 -rw-rw----. 1 mysql mysql 126 Oct 5 19:45 mysql-bin.000004 -rw-rw----. 1 mysql mysql 107 Oct 5 19:45 mysql-bin.000005 -rw-rw----. 1 mysql mysql 95 Oct 5 19:45 mysql-bin.index drwx------. 2 mysql mysql 4096 Oct 5 18:15 performance_schema -rw-r-----. 1 mysql root 4775 Oct 5 19:45 serv08.host.com.err -rw-rw----. 1 mysql mysql 5 Oct 5 19:45 serv08.host.com.pid drwxr-xr-x. 2 mysql mysql 4096 Oct 5 18:12 test </span>로그인 후 복사
第四步，从服务器通过change master to命令修改设置mysql> show binary logs; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 1 Current database: *** NONE *** +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 27320 | | mysql-bin.000002 | 1035309 | | mysql-bin.000003 | 126 | | mysql-bin.000004 | 126 | | mysql-bin.000005 | 107 | +------------------+-----------+ 5 rows in set (0.00 sec) mysql> reset master; Query OK, 0 rows affected (0.02 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 107 | +------------------+-----------+ 1 row in set (0.00 sec) mysql> show slave status; Empty set (0.00 sec)로그인 후 복사
第五步，开启slave。<span>mysql> change master to -> master_host='192.168.1.11', -> master_user='larry', -> master_password='larry', -> master_port=3306, -> master_log_file='mysql-bin.000001', -> master_log_pos=107;</span><span> Query OK, 0 rows affected (0.01 sec) </span>로그인 후 복사
第六步，从服务器查看是否和主服务器通信成功。如果出现 Slave_IO_Running和Slave_SQL_Running都是yes，则证明配置成功<span>mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.1.11 Master_User: larry Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 107 Relay_Log_File: serv08-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 </span><span>Slave_IO_Running: No Slave_SQL_Running: No</span><span> 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: 107 Relay_Log_Space: 107 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: NULL 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: 0 1 row in set (0.00 sec) ERROR: No query specified mysql> start slave; Query OK, 0 rows affected (0.01 sec) </span>로그인 후 복사
第七步，从服务器查看数据文件的更改<span>mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.11 Master_User: larry Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 107 Relay_Log_File: serv08-relay-bin.000002 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000001 </span><span> Slave_IO_Running: Yes Slave_SQL_Running: Yes</span><span> 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: 107 Relay_Log_Space: 410 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) ERROR: No query specified </span>로그인 후 복사
第八步，测试[root@serv08 data]# ll total 28724 -rw-rw----. 1 mysql mysql 18874368 Oct 5 19:45 ibdata1 -rw-rw----. 1 mysql mysql 5242880 Oct 5 19:45 ib_logfile0 -rw-rw----. 1 mysql mysql 5242880 Oct 5 18:16 ib_logfile1 -rw-rw----. 1 mysql mysql 78 Oct 5 19:49 master.info drwxr-xr-x. 2 mysql mysql 4096 Oct 5 18:15 mysql -rw-rw----. 1 mysql mysql 107 Oct 5 19:45 mysql-bin.000001 -rw-rw----. 1 mysql mysql 19 Oct 5 19:45 mysql-bin.index drwx------. 2 mysql mysql 4096 Oct 5 18:15 performance_schema -rw-rw----. 1 mysql mysql 51 Oct 5 19:49 relay-log.info -rw-r-----. 1 mysql root 5589 Oct 5 19:49 serv08.host.com.err -rw-rw----. 1 mysql mysql 5 Oct 5 19:45 serv08.host.com.pid -rw-rw----. 1 mysql mysql 157 Oct 5 19:49 serv08-relay-bin.000001 -rw-rw----. 1 mysql mysql 253 Oct 5 19:49 serv08-relay-bin.000002 -rw-rw----. 1 mysql mysql 52 Oct 5 19:49 serv08-relay-bin.index drwxr-xr-x. 2 mysql mysql 4096 Oct 5 18:12 test [root@serv08 data]# cat relay-log.info ./serv08-relay-bin.000002 253 mysql-bin.000001 107 [root@serv08 data]# cat master.info 18 mysql-bin.000001 107 192.168.1.11 larry larry 3306로그인 후 복사
第九步，查看进程状态<span>--serv08查看数据库</span><span> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.02 sec) </span><span>--serv01创建数据库</span><span> mysql> create database larrydb; Query OK, 1 row affected (0.00 sec) </span><span>--serv01查看数据库</span><span> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | larrydb | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.01 sec) </span><span>--serv08查看数据库，发现已经同步</span><span> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | larrydb | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) </span><span>--serv01创建表 插入数据</span><span> mysql> use larrydb; Database changed mysql> create table test(id int(11)); Query OK, 0 rows affected (0.00 sec) mysql> insert into test values(1); Query OK, 1 row affected (0.00 sec) </span><span>--serv08查看数据是否同步成功，发现数据已经同步</span><span> mysql> use larrydb; Database changed mysql> show tables; +-------------------+ | Tables_in_larrydb | +-------------------+ | test | +-------------------+ 1 row in set (0.00 sec) mysql> select * from test; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) </span>로그인 후 복사
--serv01查看进程状态 mysql> show processlist; +----+-------+--------------------+---------+-------------+------+-----------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------+--------------------+---------+-------------+------+-----------------------------------------------------------------------+------------------+ | 1 | root | localhost | larrydb | Query | 0 | NULL | show processlist | | 2 | larry | 192.168.1.18:41393 | NULL | Binlog Dump | 854 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | +----+-------+--------------------+---------+-------------+------+-----------------------------------------------------------------------+------------------+ 2 rows in set (0.00 sec) --serv08查看进程状态 mysql> show processlist; +----+-------------+-----------+---------+---------+------+-----------------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+---------+---------+------+-----------------------------------------------------------------------------+------------------+ | 1 | root | localhost | larrydb | Query | 0 | NULL | show processlist | | 2 | system user | | NULL | Connect | 880 | Waiting for master to send event | NULL | | 3 | system user | | NULL | Connect | 65 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | +----+-------------+-----------+---------+---------+------+-----------------------------------------------------------------------------+------------------+ 3 rows in set (0.00 sec)로그인 후 복사
MySQLAB双向复制
好了，MySQL AB单向复制介绍完毕。接下来想想，会有这样的应用场景。比如Master和Slave之间都要进行数据同步，那么单向复制是无法完成的，因为一个是Master，一个是Slave，只能单向操作，这就像网络里的半双工一样。既然一方可以向另一方同步数据，那么两方都做成Master 不就可以实现互相同步数据了。这就是接下来要介绍的MySQL AB双向复制。同样我们来看看MySQL AB双向复制的拓扑图。
图二 MySQL AB双向复制
既然对拓扑图和原理有所了解，我们做一个实验，介绍如何使用MySQL AB双向复制，注意该实验是在MySQL单级复制的基础上做的。
实验环境介绍
主机 IP地址 主机名 备注
serv01： 192.168.1.11 serv01.host.com master
serv08： 192.168.1.18 serv08.host.com slave01
操作系统版本：rhel server 6.1
所需要的软件包：mysql-5.5.29-linux2.6-x86_64.tar.gz
第一步，serv08创建授权用户
第二步，serv08清空日志mysql> grant replication client, replication slave on *.* to 'larry'@'192.168.1.%' identified by 'larry'; Query OK, 0 rows affected (0.01 sec)로그인 후 복사
第三步，serv01使用change master to命令修改从服务器设置mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 286 | +------------------+-----------+ 1 row in set (0.00 sec) mysql> reset master; Query OK, 0 rows affected (0.00 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 107 | +------------------+-----------+ 1 row in set (0.00 sec)로그인 후 복사
第四步，serv01开启slavemysql> show slave status; Empty set (0.00 sec) mysql> change master to -> master_host='192.168.1.18', -> master_user='larry', -> master_password='larry', -> master_port=3306, -> master_log_file='mysql-bin.000001', -> master_log_pos=107; Query OK, 0 rows affected (0.01 sec)로그인 후 복사
第五步，测试<span>mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.18 Master_User: larry Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 107 Relay_Log_File: serv01-relay-bin.000002 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000001 </span><span> Slave_IO_Running: Yes Slave_SQL_Running: Yes</span><span> 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: 107 Relay_Log_Space: 410 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: 2 1 row in set (0.00 sec) ERROR: No query specified </span>로그인 후 복사
<span>--serv01查看数据</span><span> mysql> use larrydb; Database changed mysql> select * from test; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) </span><span>--serv08插入数据</span><span> mysql> use larrydb; Database changed mysql> select * from test; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> insert into test values(2); Query OK, 1 row affected (0.00 sec) </span><span>--serv01查看数据，数据更新</span><span> mysql> select * from test; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec) </span><span>--serv01插入数据</span><span> mysql> insert into test values(3); Query OK, 1 row affected (0.01 sec) </span><span>--serv01查询数据</span><span> mysql> select * from test; +------+ | id | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) </span><span>--serv08查询数据，数据已更新</span><span> mysql> select * from test; +------+ | id | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) </span>로그인 후 복사
MySQL多级主从复制
好了，MySQL AB双向复制介绍完毕，我们又想了，不管是MySQL AB单向复制，还是MySQL 双向复制，都是双方的关系。MySQL AB单向复制可以是一对一，也就是一个Master对应一个Slave，或者一对多，也就是一个Master对应多个Slave；MySQL双向复制是一对一的关系。我们可不可以这样，实现多级关系，一个Master，接下来Slave，Slave下面还有Slave。这样做有什么好处呢？这样可以缓解数据库压力。这就是接下来要介绍的MySQL多级主从复制。多级也就是A---->B---->C，A作为主服务器，B是从服务器，B跟A建立主从关系；而且B是主服务器，C作为从服务器，B跟C建立主从关系。这样：A是主服务器，B既是主服务器，又是从服务器，C是从服务器。同样，我们来看看MySQL 多级主从复制的拓扑图：
图三 MySQL 多级主从复制
该拓扑图实现 mysql 的 A 到B 的复制，再从 B 到 C 的复制。
既然对拓扑图和原理有所了解，我们做一个实验，介绍如何使用MySQL AB双向复制：
实验环境介绍
主机 IP地址 主机名 备注
serv01： 192.168.1.11 serv01.host.com master
serv08： 192.168.1.18 serv08.host.com slave01
serv09： 192.168.1.19 serv09.host.com slave02
操作系统版本：rhel server 6.1
所需要的软件包：mysql-5.5.29-linux2.6-x86_64.tar.gz
第一步，断开双向关系。A只作为主服务器。
第二步，serv01重启服务，再次查看slave信息，发现已经不存在<span>--停止slave</span><span> mysql> stop slave; Query OK, 0 rows affected (0.00 sec) </span><span>--查看slave状态发现仍然有相关信息，我们要彻底删除，只需要把数据文件中相关文件删除即可。</span><span> mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.1.18 Master_User: larry Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 587 Relay_Log_File: serv01-relay-bin.000006 Relay_Log_Pos: 733 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: No Slave_SQL_Running: No 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: 587 Relay_Log_Space: 1036 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: NULL 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: 2 1 row in set (0.00 sec) ERROR: No query specified </span><span>--进入data目录，删除以下文件：master.info relay-log.info serv01-relay-bin.*</span><span> [root@serv01 ~]# cd /usr/local/mysql/data [root@serv01 data]# ll total 28736 -rw-rw----. 1 mysql mysql 18874368 Oct 5 22:38 ibdata1 -rw-rw----. 1 mysql mysql 5242880 Oct 5 22:38 ib_logfile0 -rw-rw----. 1 mysql mysql 5242880 Oct 5 18:16 ib_logfile1 drwx------. 2 mysql mysql 4096 Oct 5 22:36 larrydb -rw-rw----. 1 mysql mysql 79 Oct 5 23:24 master.info drwxr-xr-x. 2 mysql mysql 4096 Oct 5 18:15 mysql -rw-rw----. 1 mysql mysql 690 Oct 5 22:34 mysql-bin.000001 -rw-rw----. 1 mysql mysql 970 Oct 5 22:38 mysql-bin.000002 -rw-rw----. 1 mysql mysql 38 Oct 5 22:34 mysql-bin.index drwx------. 2 mysql mysql 4096 Oct 5 18:15 performance_schema -rw-rw----. 1 mysql mysql 53 Oct 5 23:24 relay-log.info -rw-r-----. 1 mysql root 5309 Oct 5 23:24 serv01.host.com.err -rw-rw----. 1 mysql mysql 5 Oct 5 22:34 serv01.host.com.pid -rw-rw----. 1 mysql mysql 303 Oct 5 22:35 serv01-relay-bin.000005 -rw-rw----. 1 mysql mysql 733 Oct 5 22:37 serv01-relay-bin.000006 -rw-rw----. 1 mysql mysql 52 Oct 5 22:35 serv01-relay-bin.index -rw-r-----. 1 mysql mysql 2209 Oct 5 18:16 serv08.host.com.err drwxr-xr-x. 2 mysql mysql 4096 Oct 5 18:12 test [root@serv01 data]# rm -rf master.info relay-log.info serv01-relay-bin.* </span>로그인 후 복사
第三步，serv08查看slave状态[root@serv01 data]# /etc/init.d/mysqld restart Shutting down MySQL.... SUCCESS! Starting MySQL.. SUCCESS! [root@serv01 opt]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.29-log Source distribution mysql> show slave status \G; Empty set (0.00 sec) ERROR: No query specified로그인 후 복사
第四步，serv09搭建相同版本的MySQL，修改server-id，启动服务<span>mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.11 Master_User: larry Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 107 Relay_Log_File: serv08-relay-bin.000007 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000003 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: 107 Relay_Log_Space: 556 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) ERROR: No query specified </span><span>--如果查看slave状态出错，我们重启服务</span><span> [root@serv08 ~]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS! </span>로그인 후 복사
第五步，serv01插入数据[root@serv09 ~]# vim /etc/my.cnf [root@serv09 ~]# cat /etc/my.cnf | grep server-id server-id = 3 [root@serv09 ~]# /etc/init.d/mysqld start Starting MySQL.. SUCCESS!로그인 후 복사
第六步，serv08查看serv01插入的数据是否记录到日志文件。可以发现，和serv01建立关系的延时日志文件中有相关记录，而主日志文件mysql-bin.000003中没有相关记录mysql> use larrydb; Database changed mysql> select * from t2; +----+---------+ | id | name | +----+---------+ | 1 | larry01 | | 3 | larry02 | | 4 | larry03 | | 6 | larry04 | | 7 | larry05 | +----+---------+ 5 rows in set (0.00 sec) mysql> insert into t2(name) values('larry07'); Query OK, 1 row affected (0.01 sec)로그인 후 복사
第七步，我们要把serv08的数据同步到serv09，因为serv08中的mysql-bin.000003文件没有相关记录，所以不能通过日志文件同步，我们只有先serv08导出数据，然后serv09导入数据，再把log_slave_updates打开[root@serv08 data]# mysqlbinlog serv08-relay-bin.000009 | grep insert -i --color /*!40019 SET @@session.max_insert_delayed_threads=0*/; SET INSERT_ID=9/*!*/; insert into t2(name) values('larry07') [root@serv08 data]# mysqlbinlog mysql-bin.000003 | grep larry07로그인 후 복사
第八步，serv01插入测试数据，我们看到打开这个参数后mysql-bin.000004和serv08-relay-bin.000011都有相关的插入数据的记录<span>--导出数据</span><span> [root@serv08 data]# mysqldump --help --verbose | grep database [root@serv08 data]# mysqldump --databases larrydb > larrydb.sql </span><span>--拷贝数据文件</span><span> [root@serv08 data]# scp larrydb.sql 192.168.1.19:/opt root@192.168.1.19's password: larrydb.sql 100% 2612 2.6KB/s 00:00 </span><span>--serv09导入数据</span><span> mysql> source /opt/larrydb.sql; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.01 sec) Database changed Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> use larrydb; Database changed mysql> show tables; +-------------------+ | Tables_in_larrydb | +-------------------+ | t2 | | test | +-------------------+ 2 rows in set (0.01 sec) mysql> select * from t2; +----+---------+ | id | name | +----+---------+ | 1 | larry01 | | 3 | larry02 | | 4 | larry03 | | 6 | larry04 | | 7 | larry05 | | 9 | larry07 | +----+---------+ 6 rows in set (0.01 sec) </span><span><strong>--serv08修改配置文件，打开log_slave_updates，重启MySQL服务</strong></span><span> mysql> show variables like '%update%'; +-----------------------------------------+-------+ | Variable_name | Value | +-----------------------------------------+-------+ | binlog_direct_non_transactional_updates | OFF | | log_slave_updates | OFF | | low_priority_updates | OFF | | sql_low_priority_updates | OFF | | sql_safe_updates | OFF | +-----------------------------------------+-------+ 5 rows in set (0.00 sec) [root@serv08 data]# vim /etc/my.cnf [root@serv08 data]# cat /etc/my.cnf | grep log_slave_updates log_slave_updates=1 [root@serv08 data]# /etc/init.d/mysqld restart Shutting down MySQL.... SUCCESS! Starting MySQL.. SUCCESS! </span><span>--serv08</span><span> mysql> show variables like "%update%"; +-----------------------------------------+-------+ | Variable_name | Value | +-----------------------------------------+-------+ | binlog_direct_non_transactional_updates | OFF | </span><span>| log_slave_updates | ON |</span><span> | low_priority_updates | OFF | | sql_low_priority_updates | OFF | | sql_safe_updates | OFF | +-----------------------------------------+-------+ 5 rows in set (0.00 sec) </span>로그인 후 복사
第九步，serv08创建授权用户mysql> insert into t2(name) values('larry08'); Query OK, 1 row affected (0.00 sec) mysql> select * from t2; +----+---------+ | id | name | +----+---------+ | 1 | larry01 | | 3 | larry02 | | 4 | larry03 | | 6 | larry04 | | 7 | larry05 | | 9 | larry07 | | 11 | larry08 | +----+---------+ 7 rows in set (0.00 sec) [root@serv08 data]# mysqlbinlog mysql-bin.000004 | grep larry use `larrydb`/*!*/; insert into t2(name) values('larry08') [root@serv08 data]# mysqlbinlog serv08-relay-bin.000011 | grep larry use `larrydb`/*!*/; insert into t2(name) values('larry08')로그인 후 복사
第十步，serv09通过change master to修改slave配置，然后启动slave，查看slave状态，查看数据，发现已经从serv08更新过来mysql> select user,password,host from mysql.user where user='larry'; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 3 Current database: larrydb +-------+-------------------------------------------+-------------+ | user | password | host | +-------+-------------------------------------------+-------------+ | larry | *0CDC8D34246E22649D647DB04E7CCCACAB4368B6 | 192.168.1.% | +-------+-------------------------------------------+-------------+ 1 row in set (0.00 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 1046 | | mysql-bin.000002 | 606 | | mysql-bin.000003 | 126 | | mysql-bin.000004 | 335 | +------------------+-----------+ 4 rows in set (0.00 sec)로그인 후 복사
第十一步，serv01插入数据，可以看到serv08 serv09都已经同步过去了<span>mysql> change master to -> master_host='192.168.1.18', -> master_user='larry', -> master_password='larry', -> master_port=3306, -> master_log_file='mysql-bin.000003', -> master_log_pos=<strong>126</strong>;</span><span> Query OK, 0 rows affected (0.03 sec) mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.1.18 Master_User: larry Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 126 Relay_Log_File: serv09-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000003 </span><span> Slave_IO_Running: No Slave_SQL_Running: No</span><span> 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: 126 Relay_Log_Space: 107 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: NULL 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: 0 1 row in set (0.00 sec) ERROR: No query specified mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.18 Master_User: larry Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 335 Relay_Log_File: serv09-relay-bin.000003 Relay_Log_Pos: 481 Relay_Master_Log_File: mysql-bin.000004 </span><span> Slave_IO_Running: Yes Slave_SQL_Running: Yes</span><span> 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: 335 Relay_Log_Space: 784 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: 2 1 row in set (0.00 sec) ERROR: No query specified mysql> select * from larrydb.t2; +----+---------+ | id | name | +----+---------+ | 1 | larry01 | | 3 | larry02 | | 4 | larry03 | | 6 | larry04 | | 7 | larry05 | | 9 | larry07 | | 11 | larry08 | +----+---------+ 7 rows in set (0.00 sec) </span>로그인 후 복사
<span>--serv01</span><span> mysql> insert into t2(name) values('larry09'); Query OK, 1 row affected (0.00 sec) mysql> select * from t2; +----+---------+ | id | name | +----+---------+ | 1 | larry01 | | 3 | larry02 | | 4 | larry03 | | 6 | larry04 | | 7 | larry05 | | 9 | larry07 | | 11 | larry08 | | 13 | larry09 | +----+---------+ 8 rows in set (0.00 sec) </span><span>--serv08</span><span> mysql> select * from larrydb.t2; +----+---------+ | id | name | +----+---------+ | 1 | larry01 | | 3 | larry02 | | 4 | larry03 | | 6 | larry04 | | 7 | larry05 | | 9 | larry07 | | 11 | larry08 | | 13 | larry09 | +----+---------+ 8 rows in set (0.00 sec) </span><span>--serv09</span><span> mysql> select * from larrydb.t2; +----+---------+ | id | name | +----+---------+ | 1 | larry01 | | 3 | larry02 | | 4 | larry03 | | 6 | larry04 | | 7 | larry05 | | 9 | larry07 | | 11 | larry08 | | 13 | larry09 | +----+---------+ 8 rows in set (0.00 sec)</span>로그인 후 복사
解决AB双向复制主键冲突
在进行MySQLAB双向复制时，如果一张表的主键是自增的，会出现问题。主服务器和从服务器在插入数据时会发生主键冲突，比如A服务器插入一条数据，id为5，B服务器同步过去，但是B服务器插入数据ID也可能是5，就这会引起主键冲突，导致数据不能插入。因此，我们需要解决这个问题。解决办法是主键间隔设置，通过设置主键步长，比如A（13 5 7），B（2 4 6 8），有几台机器步长就为几。接下来的实验是在MySQLAB双向复制的基础上做的。
第一步，serv08创建测试表，插入数据，查看数据
第二步，serv01查看数据mysql> create table t2(id int auto_increment primary key,name varchar(30)); Query OK, 0 rows affected (0.00 sec) mysql> desc t2; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(30) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> insert into t2(name) values('larry01'); Query OK, 1 row affected (0.00 sec) mysql> insert into t2(name) values('larry02'); Query OK, 1 row affected (0.01 sec) mysql> select * from t2; +----+---------+ | id | name | +----+---------+ | 1 | larry01 | | 2 | larry02 | +----+---------+ 2 rows in set (0.00 sec)로그인 후 복사
第二步，serv01和serv08修改配置文件，并重启服务mysql> select * from t2; +----+---------+ | id | name | +----+---------+ | 1 | larry01 | | 2 | larry02 | +----+---------+ 2 rows in set (0.00 sec) mysql> drop table t2; Query OK, 0 rows affected (0.01 sec)로그인 후 복사
第三步，serv01再次模拟数据<span>[root@serv01 opt]# vim /etc/my.cnf [root@serv01 opt]# cat /etc/my.cnf | grep auto_incre </span><span>auto_increment_increment=2 auto_increment_offset=1</span><span> [root@serv01 opt]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS! [root@serv08 data]# vim /etc/my.cnf [root@serv08 data]# cat /etc/my.cnf | grep auto_incre </span><span>auto_increment_increment=2 auto_increment_offset=2</span><span> [root@serv08 data]# /etc/init.d/mysqld restart Shutting down MySQL. SUCCESS! Starting MySQL.. SUCCESS! </span>로그인 후 복사
<span>mysql> use larrydb; Database changed mysql> show tables; +-------------------+ | Tables_in_larrydb | +-------------------+ | test | +-------------------+ 1 row in set (0.00 sec) mysql> create table t2(id int(11) primary key auto_increment, name varchar(30)); Query OK, 0 rows affected (0.02 sec) mysql> insert into t2(name) values('larry01'); Query OK, 1 row affected (0.01 sec) mysql> insert into t2(name) values('larry02'); Query OK, 1 row affected (0.00 sec) mysql> select * from t2; +----+---------+ | id | name | +----+---------+ | 1 | larry01 | | 3 | larry02 | +----+---------+ 2 rows in set (0.00 sec) </span><span>--serv08</span><span> mysql> select * from t2; +----+---------+ | id | name | +----+---------+ | 1 | larry01 | | 3 | larry02 | +----+---------+ 2 rows in set (0.00 sec) mysql> insert into t2(name) values('larry03'); Query OK, 1 row affected (0.00 sec) mysql> insert into t2(name) values('larry04'); Query OK, 1 row affected (0.00 sec) mysql> select * from t2; +----+---------+ | id | name | +----+---------+ | 1 | larry01 | | 3 | larry02 | | 4 | larry03 | | 6 | larry04 | +----+---------+ 4 rows in set (0.00 sec) </span><span>--serv01</span><span> mysql> insert into t2(name) values('larry05'); Query OK, 1 row affected (0.00 sec) mysql> select * from t2; +----+---------+ | id | name | +----+---------+ | 1 | larry01 | | 3 | larry02 | | 4 | larry03 | | 6 | larry04 | | 7 | larry05 | +----+---------+ 5 rows in set (0.00 sec) </span><span>--serv08</span><span> mysql> select * from t2; +----+---------+ | id | name | +----+---------+ | 1 | larry01 | | 3 | larry02 | | 4 | larry03 | | 6 | larry04 | | 7 | larry05 | +----+---------+ 5 rows in set (0.00 sec) </span>로그인 후 복사
<span><span>我的邮箱</span></span><span>：</span>wgbno27@163.com <span> <span>新浪微博</span></span><span>：</span>@Wentasy27 <span>微信公众平台</span>：JustOracle（微信号：justoracle） <span>数据库技术交流群</span>：336882565（加群时验证 From CSDN XXX） <span><strong>All is Well</strong></span> <span><strong>2013年10月27日</strong></span> <span><strong>By Larry Wen</strong></span>로그인 후 복사
 |
 
|
| @Wentasy |
본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
핫 AI 도구
Undresser.AI Undress
사실적인 누드 사진을 만들기 위한 AI 기반 앱
AI Clothes Remover
사진에서 옷을 제거하는 온라인 AI 도구입니다.
Undress AI Tool
무료로 이미지를 벗다
Clothoff.io
AI 옷 제거제
AI Hentai Generator
AI Hentai를 무료로 생성하십시오.
인기 기사
R.E.P.O. 에너지 결정과 그들이하는 일 (노란색 크리스탈)
2 몇 주 전
By 尊渡假赌尊渡假赌尊渡假赌
Repo : 팀원을 부활시키는 방법
1 몇 달 전
By 尊渡假赌尊渡假赌尊渡假赌
헬로 키티 아일랜드 어드벤처 : 거대한 씨앗을 얻는 방법
4 몇 주 전
By 尊渡假赌尊渡假赌尊渡假赌
스플릿 소설을이기는 데 얼마나 걸립니까?
3 몇 주 전
By DDD
R.E.P.O. 파일 저장 위치 : 어디에 있고 그것을 보호하는 방법은 무엇입니까?
3 몇 주 전
By DDD
뜨거운 도구
메모장++7.3.1
사용하기 쉬운 무료 코드 편집기
SublimeText3 중국어 버전
중국어 버전, 사용하기 매우 쉽습니다.
스튜디오 13.0.1 보내기
강력한 PHP 통합 개발 환경
드림위버 CS6
시각적 웹 개발 도구
SublimeText3 Mac 버전
신 수준의 코드 편집 소프트웨어(SublimeText3)
뜨거운 주제
Gmail 이메일의 로그인 입구는 어디에 있나요?
7350
15
7350
15
자바 튜토리얼
1628
14
1628
14
Cakephp 튜토리얼
1353
52
1353
52
라라벨 튜토리얼
1265
25
1265
25
PHP 튜토리얼
1214
29
1214
29
PHP의 빅데이터 구조 처리 능력
May 08, 2024 am 10:24 AM
빅 데이터 구조 처리 기술: 청킹(Chunking): 데이터 세트를 분할하고 청크로 처리하여 메모리 소비를 줄입니다. 생성기: 전체 데이터 세트를 로드하지 않고 데이터 항목을 하나씩 생성하므로 무제한 데이터 세트에 적합합니다. 스트리밍: 파일을 읽거나 결과를 한 줄씩 쿼리하므로 대용량 파일이나 원격 데이터에 적합합니다. 외부 저장소: 매우 큰 데이터 세트의 경우 데이터를 데이터베이스 또는 NoSQL에 저장합니다.
PHP에서 MySQL 쿼리 성능을 최적화하는 방법은 무엇입니까?
Jun 03, 2024 pm 08:11 PM
선형 복잡성에서 로그 복잡성까지 조회 시간을 줄이는 인덱스를 구축하여 MySQL 쿼리 성능을 최적화할 수 있습니다. SQL 삽입을 방지하고 쿼리 성능을 향상하려면 PREPAREDStatements를 사용하세요. 쿼리 결과를 제한하고 서버에서 처리되는 데이터의 양을 줄입니다. 적절한 조인 유형 사용, 인덱스 생성, 하위 쿼리 사용 고려 등 조인 쿼리를 최적화합니다. 쿼리를 분석하여 병목 현상을 식별하고, 캐싱을 사용하여 데이터베이스 로드를 줄이고, 오버헤드를 최소화합니다.
PHP에서 MySQL 백업 및 복원을 사용하는 방법은 무엇입니까?
Jun 03, 2024 pm 12:19 PM
PHP에서 MySQL 데이터베이스를 백업하고 복원하는 작업은 다음 단계에 따라 수행할 수 있습니다. 데이터베이스 백업: mysqldump 명령을 사용하여 데이터베이스를 SQL 파일로 덤프합니다. 데이터베이스 복원: mysql 명령을 사용하여 SQL 파일에서 데이터베이스를 복원합니다.
PHP를 사용하여 MySQL 테이블에 데이터를 삽입하는 방법은 무엇입니까?
Jun 02, 2024 pm 02:26 PM
MySQL 테이블에 데이터를 삽입하는 방법은 무엇입니까? 데이터베이스에 연결: mysqli를 사용하여 데이터베이스에 대한 연결을 설정합니다. SQL 쿼리 준비: 삽입할 열과 값을 지정하는 INSERT 문을 작성합니다. 쿼리 실행: query() 메서드를 사용하여 삽입 쿼리를 실행하면 확인 메시지가 출력됩니다.
PHP에서 MySQL 저장 프로시저를 사용하는 방법은 무엇입니까?
Jun 02, 2024 pm 02:13 PM
PHP에서 MySQL 저장 프로시저를 사용하려면: PDO 또는 MySQLi 확장을 사용하여 MySQL 데이터베이스에 연결합니다. 저장 프로시저를 호출하는 문을 준비합니다. 저장 프로시저를 실행합니다. 결과 집합을 처리합니다(저장 프로시저가 결과를 반환하는 경우). 데이터베이스 연결을 닫습니다.
MySQL 8.4에서 mysql_native_password가 로드되지 않음 오류를 수정하는 방법
Dec 09, 2024 am 11:42 AM
MySQL 8.4(2024년 최신 LTS 릴리스)에 도입된 주요 변경 사항 중 하나는 "MySQL 기본 비밀번호" 플러그인이 더 이상 기본적으로 활성화되지 않는다는 것입니다. 또한 MySQL 9.0에서는 이 플러그인을 완전히 제거합니다. 이 변경 사항은 PHP 및 기타 앱에 영향을 미칩니다.
PHP를 사용하여 MySQL 테이블을 만드는 방법은 무엇입니까?
Jun 04, 2024 pm 01:57 PM
PHP를 사용하여 MySQL 테이블을 생성하려면 다음 단계가 필요합니다. 데이터베이스에 연결합니다. 데이터베이스가 없으면 작성하십시오. 데이터베이스를 선택합니다. 테이블을 생성합니다. 쿼리를 실행합니다. 연결을 닫습니다.
오라클 데이터베이스와 mysql의 차이점
May 10, 2024 am 01:54 AM
Oracle 데이터베이스와 MySQL은 모두 관계형 모델을 기반으로 하는 데이터베이스이지만 호환성, 확장성, 데이터 유형 및 보안 측면에서 Oracle이 우수하고, MySQL은 속도와 유연성에 중점을 두고 중소 규모 데이터 세트에 더 적합합니다. ① Oracle은 광범위한 데이터 유형을 제공하고, ② 고급 보안 기능을 제공하고, ③ 엔터프라이즈급 애플리케이션에 적합하고, ① MySQL은 NoSQL 데이터 유형을 지원하고, ② 보안 조치가 적고, ③ 중소 규모 애플리케이션에 적합합니다.
