MySQL数据库集群实战

一、数据库集群使用场景1.随着访问量的不断增加，单台MySQL数据库服务器压力不断地增加，需要对MySQL进行优化和架构改造，如果MySQL优化不能明显改善压力，可以使用高可用、主从复制、读写分离出来、拆分库、拆分表等方法来进行优化。2.MySQL主从复制集群在中小企业、大型企业中被广泛应用，MySQL主从复制的目的实现数据冗余备份，将master数据库数据定时同步至slave库中，一旦master数据库宕机，可以将web应用数据库配置快速切换到salve数据库，确保Web应用有较高的可用率，MySQL主从复制架构图如图1-1所示。二、MySQL主从复制实战MySQL主从复制环境构建至少需要2台服务器，可以配置1主多从、多主多从，以1主1从为例，MySQL主从复制架构实战步骤如下：1.在虚拟机上克隆一台CentOS主机 2.开启两台CentOS 7主机，并做相关配置1)配置两台CentOS主机名称[root@localhost sky9890]# hostnamectl Static hostname: #localhost.localdomainTransient hostname: localhost.localdomain Icon name: computer-vm Chassis: vm Machine ID: 6c938bf5dc5b492088dafb0e745f01ec Boot ID: 170db1b33955402daa0ee3d6911486ba Virtualization: vmware Operating System: CentOS Linux 7 (Core) CPE OS Name: cpe:/o:centos:centos:7 Kernel: Linux 3.10.0-862.11.6.el7.x86_64 Architecture: x86-64[root@localhost sky9890]# hostnamectl set-hostname MySQL_Master#配置永久生效的主机名[root@localhost sky9890]# hostnamectl #查看主机名称，重新启动系统后生效 Static hostname: mysql_master Pretty hostname: MySQL_Master…………………………………………………………[root@localhost sky9890]# hostnamectl set-hostname MySQL_Slave
2)Master和Slave主机网络MySQL Master：192.168.153.142 #配置Master IP[root@localhost sky9890]# vim /etc/sysconfig/network-scripts/ifcfg-eth0TYPE=”Ethernet”BOOTPROTO=”static”
NAME=”ens33″
UUID=”9f75af90-bd5d-467e-b433-216456e4a49e”DEVICE=”eth0″ONBOOT=”yes”IPADDR=192.168.153.142NETMASK=255.255.255.0GATEWAY=192.168.153.2MySQL Slave：192.168.153.143 #配置Slave IP
[root@localhost sky9890]# vim /etc/sysconfig/network-scripts/ifcfg-eth0TYPE=”Ethernet”BOOTPROTO=”static”DEFROUTE=”yes”IPV4_FAILURE_FATAL=”no”IPV6INIT=”yes”IPV6_AUTOCONF=”yes”IPV6_DEFROUTE=”yes”IPV6_FAILURE_FATAL=”no”IPV6_ADDR_GEN_MODE=”stable-privacy”NAME=”ens33″DEVICE=”eth0″ONBOOT=”yes”IPADDR=192.168.153.143NETMASK=255.255.255.0GATEWAY=192.168.153.23.MySQL Master配置[root@mysql_master sky9890]# vim /etc/my.cnf [client]port = 3306socket = /tmp/mysql.sock[mysqld]
port = 3306socket = /tmp/mysql.sockskip-external-lockingkey_buffer_size = 256Mmax_allowed_packet = 1Mtable_open_cache = 256sort_buffer_size = 1Mread_buffer_size = 1Mread_rnd_buffer_size = 4Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size= 16Mthread_concurrency = 8log-bin=mysql-binbinlog_format=mixedserver-id = 1[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash[myisamchk]key_buffer_size = 128Msort_buffer_size = 128Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout4.MySQL Master创建用户及授权
[root@mysql_master etc]# useradd testtongbu[root@mysql_master etc]# passwd testtongbu[root@mysql_master etc]# mysql -uroot -pMySQL [(none)]> grant replication slave on *.* to ‘testtongbu ‘@’%’ identified by ‘12345678’;MySQL [(none)]> show master status;
+——————+———-+————–+——————+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+——————+———-+————–+——————+| mysql-bin.000008 | 867 | | |+——————+———-+————–+——————+5.MySQL Slave配置[root@mysql_master sky9890]# vim /etc/my.cnf [client]port = 3306socket = /tmp/mysql.sock[mysqld]
port = 3306socket = /tmp/mysql.sockskip-external-lockingkey_buffer_size = 256Mmax_allowed_packet = 1Mtable_open_cache = 256sort_buffer_size = 1Mread_buffer_size = 1Mread_rnd_buffer_size = 4Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size= 16Mthread_concurrency = 8#log-bin=mysql-bin#binlog_format=mixedserver-id = 2[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash[myisamchk]key_buffer_size = 128Msort_buffer_size = 128Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout[root@mysql_slave sky9890]#MySQL [(none)]> change master to master_host=’192.168.153.142′, master_port=3306, master_user=’testtongbu’,master_passwork=’12345678′, master_log_file=’mysql-bin.000008′, master_log_pos=867;
MySQL [(none)]> slave start;
MySQL [(none)]> show slave statusG*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.153.142 Master_User: testtongbu Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 867 Relay_Log_File: mysql_slave-relay-bin.000005 Relay_Log_Pos: 596 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes #IO、SQL线程状态为Yes,代表slave已正常连接master实现同步 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: 867 Relay_Log_Space: 904 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: 0Master_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: 16.测试同步结果MySQL Master操作：MySQL [(none)]> create database tongbu_test charset=utf8;MySQL [(none)]> use tongbu_test;MySQL [tongbu_test]> create table test(id varchar(20),name varchar(20));
MySQL [tongbu_test]> show tables;
MySQL [tongbu_test]> create table student(id varchar(20),name varchar(20));
ERROR 1223 (HY000): Can’t execute the query because you have a conflicting read lockMySQL [tongbu_test]> unlock tables;MySQL [tongbu_test]> create table student(id varchar(20),name varchar(20));
MySQL [(none)]> insert into student values(“001″,”吴氏亲宗”);MySQL Slave操作：通过MySQL Slave测试数据来看，主从数据库同步成功。7.MySQL主从同步排错思路1)server-id,主从不能相同。2)slave指定master IP、用户名、密码、bin-log文件名及position的信息要一致。3)Slave_IO_Runngin:Yes Slave_SQL_Runngin:Yes，只有这两个状态都为Yes,才算是正从同步成功。4)当主从产生延迟后，如何忽略错误后，继续同步？MySQL Master:MySQL [(none)]> flush tables with read block; #将数据库设置为全局读锁，不允许写入新数据。MySQL Slave:MySQL [tongbu_test]> stop slave;MySQL [tongbu_test]> set global sql_salve_skip_counter =1MySQL [tongbu_test]> start slave;注意以上几步至少要操作一次，有可能要两次才能解决问题。最后将master端解锁：MySQL [(none)]> unlock tables;

相关推荐: 如何查询php mysql的数据

如何查询php mysql的数据？针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。php mysql查询数据的方法：首先创建php mysql连接；然后设置SQL语句，用于从数据表中读取字段信息；…