wordpress数据库主机填什么-Wordpress教程-爱模板网

wordpress数据库主机填什么

2024-04-24 15:27:37

mariadb主从部署

一、准备

主机

作用

主机1

10.100.1.10

主节点

主机2

10.100.1.11

从节点

二、原理三、mariadb部署3.1、tar包离线编译部署方式

1、下载mariadb安装包

2、上传到/opt目录

[root@master opt]# ll

total 331204

-rw-r--r--. 1 root root 339149067 May 13 10:17 mariadb-10.0.38-linux-x86_64.tar.gz

3、创建mariadb安装目录，并且将压缩包解压到改目录下

[root@master ~]# mkdir /usr/local/mariadb/

[root@master ~]# tar -zxvf /opt/mariadb-10.0.38-linux-x86_64.tar.gz -C /usr/local/mariadb/

4、进入到mariadb的安装目录，重命名

[root@master ~]# cd /usr/local/mariadb/

[root@master mariadb]# mv mariadb-10.0.38-linux-x86_64 mariadb-10.0.38

5、创建mysql用户

[root@master mariadb]# useradd -s /sbin/nologin -M mysql

6、mariadb指定路径和用户初始化

root@master mariadb]# cd mariadb-10.0.38/

[root@master mariadb-10.0.38]# ./scripts/mysql_install_db --basedir=/usr/local/mariadb/mariadb-10.0.38 --datadir=/usr/local/mariadb/mariadb-10.0.38/data/ --user=mysql

7、将原先的配置进行移动备份，将配置文件模板移动到/etc下

[root@master mariadb-10.0.38]# mv /etc/my.cnf /etc/my.cnf.bak

[root@master mariadb-10.0.38]# cp support-files/my-innodb-heavy-4G.cnf /etc/my.cnf

8、编辑配置文件

vim /etc/my.cnf

主节点：

[mysqld]

basedir=/usr/local/mariadb/mariadb-10.0.38

datadir=/usr/local/mariadb/mariadb-10.0.38/data

server-id = 1 #唯一标识

g-bin = master-bin #开启mysql binlog功能

binlog_format = ROW # binlog记录内容的方式，记录被操作的每一行

binlog-do-db = wordpress # 指定需要复制的数据库名，如果备份多个数据库，重复设置这个选项即可

备节点

mysqld]

basedir=/usr/local/mariadb/mariadb-10.0.38

datadir=/usr/local/mariadb/mariadb-10.0.38/data

server-id = 2 #唯一标识

g-bin = master-bin #开启mysql binlog功能

binlog_format = ROW # binlog记录内容的方式，记录被操作的每一行

binlog-do-db = wordpress # 指定需要复制的数据库名，如果备份多个数据库，重复设置这个选项即可

8、修改mariadb的所属用户

[root@master mariadb-10.0.38]# chown -R mysql:mysql /usr/local/mariadb/mariadb-10.0.38/

9、将启动脚本移动

[root@master mariadb-10.0.38]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld

10、启动mariadb

[root@master mariadb-10.0.38]# /etc/init.d/mysqld start

11、创建软链接

[root@master ~]# ln -s /usr/local/mariadb/mariadb-10.0.38/bin/mysql /usr/bin

[root@master ~]# ln -s /usr/local/mariadb/mariadb-10.0.38/bin/mysqladmin /usr/bin

11、初始化数据库密码

[root@master ~]# mysqladmin -u root -h localhost password '123456'

3.2、rpm包离线部署

1、上传所有的RPM包到opt目录

2、使用命令本地安装mariadb

[root@master opt]# yum localinstall *.rpm -y

3、启动mariadb

[root@master opt]# systemctl start mariadb

[root@master opt]# systemctl enable mariadb

4、设置root密码

[root@master opt]# mysqladmin -u root password 123456

5、登录mariadb数据库

[root@master opt]# mysql -uroot -p123456

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 30

Server version: 10.3.15-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

5、修改数据库配置

主数据库

[root@master ~]# vim /etc/my.cnf.d/server.cnf

[mysqld]

server-id = 1

log-bin=mysql-bin

从数据库

[root@node1 ~]# vim /etc/my.cnf.d/server.cnf

[mysqld]

server-id = 2

四、数据库同步操作

主数据库

1、登录数据库

[root@master ~]# mysql -uroot -p123456

2、创建slave用户，并设置密码，然后赋予权限

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by '123456';

3、刷新

MariaDB [(none)]> flush privileges;

4、查看master状态

MariaDB [(none)]> show master status;

+------------------+----------+-----------------+------------------+

+------------------+----------+-----------------+------------------+

+------------------+----------+-----------------+------------------+

1 row in set (0.00 sec)

从数据库

1、建立连接

MariaDB [(none)]>

CHANGE MASTER TO MASTER_HOST='10.100.1.10', MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=638; （必须和master一样）

2、启动连接

MariaDB [(none)]> start slave;

3、验证查看（有两个连接为yes，则表示成功）

MariaDB [(none)]> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.100.1.10

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000008

Read_Master_Log_Pos: 439

Relay_Log_File: node1-relay-bin.000006

Relay_Log_Pos: 726

Relay_Master_Log_File: mysql-bin.000008

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

五、测试

1、在主节点上创建一个ceshi的数据库

MariaDB [(none)]> create database woredpress123;

Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| ceshi |

| information_schema |

| mysql |

| performance_schema |

| test |

| wordpress |

| wordpress123 |

| xiaoyang |

+--------------------+

2、在从节点查看是否同步过来

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| ceshi |

| information_schema |

| mysql |

| performance_schema |

| test |

| wordpress |

| wordpress123 |

| xiaoyang |

+--------------------+

陕西耀州

湖北神狐

六、数据库简单操作

1、创建数据库

MariaDB [(none)]> create database xiaoyang;

2、创建表

MariaDB [(none)]>use xiaoyang;

MariaDB [xiaoyang]> create table test(id int,name varchar(10),mail varchar(30));

MariaDB [xiaoyang]> desc test;

3、向表内插入数据

MariaDB [xiaoyang]> insert into test(id,name,mail) values(1,'uos1','uos1@uos.com');

4、查看数据

MariaDB [xiaoyang]> select * from test;

+------+------+--------------+

| id | name | mail |

+------+------+--------------+

| 1 | uos1 | uos1@uos.com |

5、查看从数据库是否有数据

MariaDB [xiaoyang]> select * from test;

+------+------+--------------+

| id | name | mail |

+------+------+--------------+

| 1 | uos1 | uos1@uos.com |

+------+------+--------------+

sharding-jdbc的读写分离，数据库主从同步实践

前段时间看到sharding-jdbc这个东西，我就用它来实践一下读写分离，下面是我要下面的三个步骤来写这篇文章

1，弄一个基础框架spring boot + mybatis generator

2，整合sharding-jdbc，实现读写分离

3，实现主从数据库同步数据

首先我们来简单的将一下第一，二步，因为比较简单，第三步对于自己来说相当复杂一点。

第一步：

自己简单的配置一个spring boot项目，然后整合mybatis。这里注重讲一下mybatis generator

a:首先我们先加入相应的jar

<dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> </dependency>  <dependency> <groupId>org.mybatis.generator</groupId> <artifactId>mybatis-generator-core</artifactId> <version>1.3.2</version> </dependency>  <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency>

b:然后在配置相应的generatorConfig.xml,想要查看相应的配置详情，大家可以查看网上，或者参考我自己的例子（后面会附上我的仓库地址）

c:写main方法去根据配置文件生产文件

import org.mybatis.generator.api.MyBatisGenerator;import org.mybatis.generator.config.Configuration;import org.mybatis.generator.config.xml.ConfigurationParser;import org.mybatis.generator.exception.InvalidConfigurationException;import org.mybatis.generator.exception.XMLParserException;import org.mybatis.generator.internal.DefaultShellCallback;import java.io.File;import java.io.IOException;import java.io.InputStream;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;public class GenMain { public static void main(String[] args) { List<String> warnings = new ArrayList<String>(); boolean overwrite = true; String genCfg = "/generatorConfig.xml"; ClassLoader load = Thread.currentThread().getContextClassLoader(); InputStream is = load.getResourceAsStream(genCfg); File configFile = new File(GenMain.class.getResource(genCfg).getFile());// ClassLoader load = Thread.currentThread().getContextClassLoader();// InputStream is = load.getResourceAsStream(genCfg); ConfigurationParser cp = new ConfigurationParser(warnings); Configuration config = null; try { config = cp.parseConfiguration(configFile); } catch (IOException e) { e.printStackTrace(); } catch (XMLParserException e) { e.printStackTrace(); } DefaultShellCallback callback = new DefaultShellCallback(overwrite); MyBatisGenerator myBatisGenerator = null; try { myBatisGenerator = new MyBatisGenerator(config, callback, warnings); } catch (InvalidConfigurationException e) { e.printStackTrace(); } try { myBatisGenerator.generate(null); } catch (SQLException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (InterruptedException e) { e.printStackTrace(); } }}

注意的点：

Mapper.xml文件如果不放在resources文件夹中的话，编译的时候是不会去加载该配置的，需要加如下配置在mavenbuild标签中 <resources> <resource> <directory>src/main/java</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> </resource> </resources>

这样就能正常批量生成文件了

第二步：sharding-jdbc配置，实现读写分离（这里只是简单的实现读写分离，一些sharding-jdbc具体的自己另行去学习）

a:加jar

<dependency> <groupId>io.shardingjdbc</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>2.0.0.M3</version> </dependency>  <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.10</version> </dependency>

b:配置相应的sharding-jdbc(具体看我后面放的git项目地址)

这样就能实现读写分离了，很简单

第三步：主从数据库同步

我目前是在同一台物理机上部署2套数据库服务，这里我们命名为A(主)，B（从）。我们先从A，B的配置说起吧

先附上A，B数据库的配置文件

# For advice on how to change settings please see# # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the# *** default location during install, and will be replaced if you# *** upgrade to a newer version of MySQL.[mysqld]# Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.# innodb_buffer_pool_size = 128M# Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin##server_id和log_bin两项即可，其它配置都是可配可不配port=5506# 唯一标志idserver-id=1#开启二进制日志log-bin=mysql-bin#binlog-do-db=wordpress是表示只备份wordpress。#binlog_ignore_db=mysql表示忽略备份mysql。#不加binlog-do-db和binlog_ignore_db，那就表示备份全部数据库#binlog-do-db=wordpress#binlog_ignore_db=mysql# Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed, experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysql]# 设置mysql客户端默认字符集default-character-set=utf8 [mysqld]server-id=2#开启二进制日志log-bin=mysql-binrelay-log=relay-bin#设置只读权限read-only =1#复制时忽略相关表或者数据库#replicate-do-db = name 只对这个数据库进行镜像处理。#replicate-ignore-table = dbname.tablename 不对这个数据表进行镜像处理。#replicate-wild-ignore-table = dbn.tablen 不对这些数据表进行镜像处理。#replicate-ignore-db = dbname 不对这个数据库进行镜像处理。replicate-ignore-db = mysqlreplicate-ignore-db = information_schema#replicate-wild-do-table = tt.admin#所要同步的数据库的单个表replicate-wild-do-table = test.user

这里有一个点很重要，就是A，Bmysql我是直接免安装版的，里面有一个my-default.ini配置文件，但是上面的配置在这个文件里面写是无法生效的，网上的意思是你还得自己新建一个my.ini,将上面的配置写到文件里面，我试了确实是要写在my.ini里面才能生效。

之后就是启动A，B服务器。在A服务器上执行如下命令，创建一个REPLICATION SLAVE权限的账号给从数据库用

mysql>CREATE USER 'user'@ 'X.X.X.X' IDENTIFIED BY 'password'; mysql>GRANT REPLICATION SLAVE ON *.* TO 'user'@'X.X.X.X' IDENTIFIED BY 'password';

然后在B服务器上执行如下脚本（将信息写入到B中的master.info文件中）

change master to master_host='127.0.0.1',master_user='mytest',master_password='password',master_port=5506,master_log_file='mysql-bin.000004';

后面的那个日志号，在主服务器上执行如下代码就可以拿到

之后就是执行启动从服务器的SLAVE服务

START SLAVE；启动 STOP SLAVE 停止

查看SLAVE的日志脚本：

mysql> SHOW SLAVE STATUS;+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+--------------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position |+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+--------------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+| Waiting for master to send event | 127.0.0.1 | mytest | 5506 | 60 | mysql-bin.000007 | 120 | relay-bin.000007 | 283 | mysql-bin.000007 | Yes | Yes | | mysql,information_schema | | | test.user | | 0 | | 0 | 120 | 613 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 | 96cb631a-831c-11e8-b47b-089e0105f2eb | C:\Users\zfh-work\Desktop\mysql-5.6.40-winx64\data\master.info | 0 | NULL | Slave has read all relay log; waiting for the slave I/O thread to update it | 86400 | | | | | | | | 0 |+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+--------------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------------+-----------+---------------------+-----------------------------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+1 row in set

到这里为止三个步骤都已经完成了

还有在实践过程中，我这边会碰到复制过程中发生某种错误导致主从复制停止，我们有以下两种方式来处理这种中断：

1，跳过该错误的event

mysql>slave stop;mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n（保险起见设置n=1） #跳过这n个eventmysql>slave start

2，修改mysql的配置文件，通过slave_skip_errors参数来跳所有错误或指定类型的错误

vi /etc/my.cnf[mysqld]#slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误#slave-skip-errors=all #跳过所有错误

附上在数据库同步过程常用的数据库脚本：

查看mysql数据库端口：

show global variables like 'port'; 查看当前使用的用户: select user(); 查看mysql server_id: show variables like 'server_id'; 查看主数据库日志：

show master logs;

怎么判断mysql的主从是否同步？(同步日志)

mysql> show slave status\G

#查看是否都为yes Slave_IO_Running Slave_SQL_Running

数据库主从复制的原理：

Salve的IO线程会读取自己的文件目录mastr.info文件中配置好的主库信息，比如说存放的有：Master数据库的用户名、密码、端口、还有Master的binlog索引位置；例如本人的master.info：

23mysql-bin.000007 (master使用的binlog日志)120127.0.0.1(master的ip)mytest （复制数据的库）password（数据库密码）5506（端口）60001800.000096cb631a-831c-11e8-b47b-089e0105f2eb864000

拿到信息之后就带着信息去链接Master的主库IO线程当主库的IO线程先检查SLave传过来的配置信息是否正确，如果正确，就拿着Slave传过来的binlog索引位置和Master库的binlog文件中最后一个索引位置进行对比，如果一致就陷入等待状态，等待Master的binlog索引位置更新；如果不一致就把Slave传过来的binlog索引位置往后的所有SQL语句包括最后一条SQL语句的索引位置发送个给Slave的IO线程； Slave的IO线程拿到信息之后，先把Master传过来的binlog索引在Slave的master.info文件中进行更新；然后再把Master传过来的SQL语句写入到relay文件中，然后继续循环执行第二个步骤； Slave的SQL线程会一直持续的观察relay日志文件中是否有改动，如果没有就继续监听；如果发现relay中有变动，那么就获取变动的内容转换为SQL语句，并且把SQL语句在Salve的数据库中进行执行

下面自己的项目，码云地址