MySQL JSON数据类型 使用虚拟列创建索引

创建表及索引:

CREATE TABLE `cartoon_data` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `category` char(4) GENERATED ALWAYS AS (json_extract(`xml2json`,'$.category')) VIRTUAL,
  `xml2json` json NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `cartoon_data`
  ADD UNIQUE KEY `id` (`id`),
  ADD KEY `category` (`category`);

查询 JSON 格式字段 不使用索引 与 使用虚拟列索引 对比:

mysql> SELECT COUNT(*) FROM `cartoon_data` WHERE xml2json->'$.category' = 'cj';
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (1.63 sec)
mysql> EXPLAIN SELECT COUNT(*) FROM `cartoon_data` WHERE xml2json->'$.category' = 'cj';
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | cartoon_data | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 953622 |   100.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT COUNT(*) FROM `cartoon_data` WHERE category = '"cj"';
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.31 sec)
mysql> EXPLAIN SELECT COUNT(*) FROM `cartoon_data` WHERE category = '"cj"';
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+--------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key      | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | cartoon_data | NULL       | ref  | category      | category | 17      | const | 476811 |   100.00 | Using index |
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

MySQL-5.7 双主互备+keepalived

VIP : 192.168.56.100

Server 1 : 192.168.56.101

Server 2 : 192.168.56.102

Step 1 : 安装 MySQL(192.168.56.101)

//需删除 linux 系统自带的数据库配置文件/etc/my.cnf,否则安装过程中会默认加载此配置文件

groupadd mysql
useradd -r -g mysql -s /bin/false mysql
chown -R mysql:mysql .
bin/mysqld –initialize –user=mysql –basedir=/srv/mysql #如果安装目录不在 /url/local/mysql ,则需要使用 –basedir 指定
bin/mysql_ssl_rsa_setup –datadir=/srv/mysql/data #如果安装目录不在 /url/local/mysql ,则需要使用 –datadir 指定
chown -R root:root .
chown -R mysql:mysql data/
bin/mysqld_safe –user=mysql &
bin/mysqladmin -u root -p password #修改默认密码(注意前两步命令的输出有生成的默认密码)
cp support-files/mysql.server /etc/init.d/mysql.server
vim /etc/init.d/mysql.server #如果不是安装在 /url/local/mysql 目录,则需要编辑一下,指定 basedir 和 datadir
/etc/init.d/mysql.server stop
cp support-files/my-default.cnf my.cnf

 

vim my.cnf #添加或修改以下配置

server-id=101 # 同一集群中的 server id 不能相同,此处使用其ip末段数字
log_bin=mysql-bin
log_error=mysql-bin.err
binlog_ignore_db=mysql # 忽略同步的数据库

 

/etc/init.d/mysql.server start # 启动MySQL并执行以下SQL添加复制账号

CREATE USER ‘slave’@’192.168.56.102’ IDENTIFIED BY ‘password’;GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘slave’@’192.168.56.102’ REQUIRE NONE;

 

Step 2 : 安装 MySQL(192.168.56.102)

//重复 Step 1(将命令中的 101 替换成 102,102 替换成 101)

Step 3 : 配置主从互备

192.168.56.101 ~ MySQL 终端运行如下 SQL 查看此实例作为主库的状态

SHOW MASTER STATUS;

 

192.168.56.102 ~ MySQL 终端运行如下 SQL 配置此实例作为从库需要连接的主库信息

CHANGE MASTER TO
MASTER_HOST=’192.168.56.101′,
MASTER_USER=’slave’,
MASTER_PASSWORD=’password’,
MASTER_LOG_FILE=’mysql-bin.000003′, #这个值对应上条命令输出的 `File` 字段
MASTER_LOG_POS=154; #这个值对应上条命令输出的 `Position` 字段

 

192.168.56.102 ~ MySQL 终端运行如下 SQL 查看此实例作为主库的状态

SHOW MASTER STATUS; # 查看此实例作为主库的状态

 

192.168.56.101 ~ MySQL 终端运行如下 SQL 配置此实例作为从库需要连接的主库信息

CHANGE MASTER TO
MASTER_HOST=’192.168.56.102′,
MASTER_USER=’slave’,
MASTER_PASSWORD=’password’,
MASTER_LOG_FILE=’mysql-bin.000003′, #这个值对应上条命令输出的 `File` 字段
MASTER_LOG_POS=154; #这个值对应上条命令输出的 `Position` 字段

 

最后分别在 101 和 102 两台 MySQL 的终端执行以下命令

START SLAVE; # 启动从库复制
SHOW SLAVE STATUS; # 查看此实例作为从库的状态

 

查看上条命令的输出 Slave_IO_Running 和 Slave_SQL_Running 的值都为 Yes 则表示两台实例的互相复制进程已经可用了

以上配置是在两台数据库都是空闲的新实例中配置的 线上的实例不适用以上配置

线上的实例需要锁定数据库库以保证配置的 MASTER_LOG_FILE 和 MASTER_LOG_POS 是正确的

Step 4 : 安装 keepalived(192.168.56.101)

cd keepalived-1.2.23
./configure –prefix=/srv/keepalived && make && make install

 

vim /srv/keepalived/etc/keepalived/keepalived.conf #编辑配置文件如下

! Configuration File for keepalived
global_defs {
   router_id HA_MYSQL
}
vrrp_script chk_mysql {
    script “/etc/init.d/mysql.server status”    !定时检查MySQL状态,MySQL挂掉则将自己下线释放VIP
}
vrrp_instance VI_1 {
    state BACKUP
    interface enp0s8    !网络界面
    virtual_router_id 51
    priority 101    !权重
    advert_int 1
    nopreempt    !自己上线后不抢占当前 master 的位置,即使其权重比自己低
    virtual_ipaddress {
        192.168.56.100    !VIP
    }
    track_script {
        chk_mysql    !启用头部定义的检查MySQL状态的脚本
    }
}
virtual_server 192.168.56.100 3306 {
    delay_loop 3
    lb_algo rr
    lb_kind NAT
    persistence_timeout 60
    protocol TCP    sorry_server 192.168.56.102 3306    ! real_server 挂掉后临时使用 sorry_server 顶上
    real_server 192.168.56.101 3306 {
        weight 1
        TCP_CHECK {
            connect_port    3306
            connect_timeout 3
        }
    }
}

 

Step 5 : 安装 keepalived(192.168.56.102)

重复 Step 4(将配置中的 101 替换成 102,102 替换成 101)

Step 6 : 检验配置

分别启动两台机器上的 keepalived

使用 `ip a` 命令查看哪台机器抢到了 VIP ?

停止抢到 VIP 服务器的 MySQL 实例,去另一台机器看是否接管 VIP ?

重启或关闭抢到 VIP 的 keepalived 服务,去另一台机器看是否接管 VIP ?

直接断掉抢到 VIP 的服务器的网络,去另一台机器看是否接管 VIP ?

这个架构在同一时刻只有一台 MySQL 对外提供写服务,因此不存在混写的状态,不用另外设置自增的规则。


安装 PHP 7 :

./configure --prefix=/srv/php --enable-fpm --disable-ipv6 --with-openssl --enable-bcmath --with-curl --enable-exif --with-gd --enable-gd-native-ttf --with-mhash --enable-mbstring --with-mcrypt --with-mysqli --enable-pcntl --with-pdo-mysql --enable-shmop --enable-sockets --enable-sysvmsg --enable-sysvsem --enable-sysvshm --enable-mysqlnd && make && make install

 

php 读取 access 数据库

百度出来的教程几乎都已经过时了,因为微软的ODBC驱动升级后,连接驱动字符串变了。
如果电脑安装过 Office 软件选择了 AccessDB 功能,就不需要安装驱动了。否则,需要安装 AccessRuntime(ODBC驱动)。
完成上一步后,这个链接字符串可以在服务器ODBC数据源管理的地方查询到,如果是手动安装的 AccessRuntime ,就需要手动添加数据源。

< ?php
set_time_limit(0);
date_default_timezone_set('Asia/Shanghai');
require 'memcached.php';

$mdbPath = 'xxxxxx.mdb';
$memcacheServerIP = '192.168.100.100';
$memcacheServerPort = 11211;
$memcacheKey = 'MarketData';

while(true){
	try{
		$connection = odbc_connect("DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=$mdbPath;", null, null);
		$rs = odbc_exec($connection, 'SELECT * FROM ELE');
		$data = [];
		while($row = odbc_fetch_array($rs)){
			foreach($row as &$val){
				$val = iconv('gbk', 'utf-8', $val);
			}
			$data[] = $row;
		}
		odbc_close($connection);

		$m = new Memcached;
		$m->addServer($memcacheServerIP, $memcacheServerPort);
		$m->set($memcacheKey, $data);
		echo date('Y-m-d H:i:s'), ' Data count: ', count($m->get($memcacheKey)), "\r\n";
		$m->close();
	}catch(Exception $e){
		var_dump($e);
	}
	sleep(1);
}

MySQL使用存储过程随机获取若干条记录的ID

DROP PROCEDURE `get_rand_result`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_rand_result`(IN `i` INT UNSIGNED) NOT DETERMINISTIC READS SQL DATA SQL SECURITY DEFINER BEGIN

DECLARE mx INT;
DECLARE mi INT;
DECLARE var TEXT DEFAULT '';
DECLARE result TEXT DEFAULT '';

SELECT MAX(`id`) INTO @mx FROM `users_profile`;
SELECT MIN(`id`) INTO @mi FROM `users_profile`;

WHILE i>0 DO
SELECT FLOOR(@mi + RAND() * (@mx-@mi)) INTO @var;
SET i=i-1;
SELECT CONCAT_WS(',',@result,@var) INTO @result;
END WHILE;

SELECT @result;

END

输出结果示例:123,456,789

服务器自动备份脚本上传至百度云存储

一直以为自己管理的服务器不会出问题,没想到昨天由于误操作把数据库整个目录给删了,罪过。。。

有些数据我一直没在意,等到失去的时候才发现他们有多重要。。。

赶紧补上备份脚本

 
#!/bin/sh
BACK_PATH="/backup/"

LOG_FILE="/opt/back.log"

echo "" > $LOG_FILE

PREFIX="db-"
BACK_DB_S="blog lululearn"
for i in $BACK_DB_S; do
    old_bak=$BACK_PATH$PREFIX`date -d "3 days ago" +%Y-%m-%d`'-'$i'.sql'
    new_bak=$BACK_PATH$PREFIX`date +%F`'-'$i'.sql';
    if [ -f $old_bak ]; then
        rm -rf $old_bak
    fi
    mysqldump -u root --databases $i > $new_bak
    /opt/shell-tool-1.2/bsutil.sh -v cp $new_bak "bs://backupspace/"$PREFIX`date +%F`"-"$i".sql" >> $LOG_FILE &
done

cd /var/www

PREFIX="web-"
WEBROOT_S="blog.nt00.com www.lululearn.com qdxz.dayudong.com"
for i in $WEBROOT_S; do
    old_bak=$BACK_PATH$PREFIX`date -d "3 days ago" +%Y-%m-%d`'-'$i'.tar.gz'
    new_bak=$BACK_PATH$PREFIX`date +%F`'-'$i'.tar.gz';
    if [ -f $old_bak ]; then
        rm -rf $old_bak
    fi
    tar czf $new_bak $i
    /opt/shell-tool-1.2/bsutil.sh -v cp $new_bak "bs://backupspace/"$PREFIX`date +%F`"-"$i".tar.gz" >> $LOG_FILE &
done

安装配置 MySQL 集群

管理节点安装:192.168.234.128

第一步:

#wget mysql-cluster-gpl-7.2.6-linux2.6-x86_64.tar.gz

#tar xzf mysql-cluster-gpl-7.2.6-linux2.6-x86_64.tar.gz

#mv mysql-cluster-gpl-7.2.6-linux2.6-x86_64 /usr/local/mysql

—按照 MqSQL 目录下的安装帮助文件安装 MySQL —

第二步:

#mkdir /usr/local/mysql/mysql-cluster

#vim /usr/local/mysql-cluster/config.ini

[ndbd default]

NoOfReplicas=1

DataMemory=80M

IndexMemory=18M

[ndb_mgmd]

hostname=192.168.234.128

datadir=/usr/local/mysql/mysql-cluster

[ndbd]

hostname=192.168.234.129

datadir=/usr/local/mysql/data

[mysqld]

hostname=192.168.234.130

第三步:

首次启动 MySQL Cluster 管理节点:

#/usr/local/mysql/bin/ndb_mgmd -f /usr/local/mysql-cluster/config.ini

数据节点安装:192.168.234.129

第一步:

参照管理节点安装 第一步。

第二步:

#cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf

#vim /etc/my.cnf

[mysqld]

ndbcluster

ndb-connectstring=192.168.234.128

[mysql_cluster]

ndb-connectstring=192.168.234.128

 

#log-bin=mysql-bin

#binlog_format=mixed

#server-id      = 1

第三步:

启动数据节点:

/usr/local/mysql/bin/ndbd

SQL节点安装:192.168.234.130

第一步:

参照管理节点安装 第一步。

#cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql.server

第二步:

#cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf

#vim /etc/my.cnf

[mysqld]

ndbcluster

ndb-connectstring=192.168.234.128

[mysql_cluster]

ndb-connectstring=192.168.234.128

第三步:

启动 SQL 节点:

/etc/init.d/mysql.server start

 

参考文献:http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster.html

通过 MYSQL 的二进制日志恢复误删的数据库

Usage: mysqlbinlog [options] log-files
base64-output                     (No default value)
character-sets-dir                (No default value)
database                          (No default value)
debug-check                       FALSE
debug-info                        FALSE
disable-log-bin                   FALSE
force-if-open                     TRUE
force-read                        FALSE
hexdump                           FALSE
host                              (No default value)
local-load                        (No default value)
offset                            0
port                              3306
position                          4
read-from-remote-server           FALSE
server-id                         0
set-charset                       (No default value)
short-form                        FALSE
socket                            /var/run/mysqld/mysqld.sock
start-datetime                    (No default value)
start-position                    4
stop-datetime                     (No default value)
stop-position                     18446744073709551615
to-last-log                       FALSE
user                              (No default value)
open_files_limit                  64

定时备份数据库

crontab -e

30 2 * * * sh /srv/backdb.sh

————————————-分割线————————————-

#!/bin/sh
#backdb.sh

o_dbname=”/mysqldata/dbname-“`date -d “2 days ago” +%Y-%m-%d`”.sql”
n_dbname=”/mysqldata/dbname-“`date +%F`”.sql”
/usr/local/mysql/bin/mysqldump -u root –databases dbname > $n_dbname

if [ -f $o_dbname ]
then
 rm -rf $o_dbname
fi

————————————-分割线————————————-