Home Assistant 更换 MySQL 和 PostgreSQL 数据库

Home Assistant 默认的数据库是 sqlite,随着使用时间的增加,数据库文件越来越大,查看日志和历史的速度也变得越来越慢。曾经试着把这个 sqlite 数据库文件放到 /tmp 目录下,也就是内存中,响应速度快了很多,但是内存很快就塞满导致死机。

MySQL/MariaDB 和 PostgreSQL 则成为更好性能数据库的选择。强烈推荐使用 PostgreSQL,系统负载更低。

下面以安装在本机的数据库为例,进行简单的配置。

Home Assistant 使用 MySQL/MariaDB

1、安装 MySQL/MariaDB

2、新建数据库

mysql -u root -p

输入密码后,进入 mysql 命令行,

create database hass;

如果你使用 phpMyAdmin 管理 MySQL,新建数据库之类的操作就简单多了。

3、安装依赖软件

pip3 install mysqlclient

4、在 Home Assistant 的配置文件 configuration.yaml 中添加

recorder:
  db_url: mysql://user:[email protected]_IP/DB_NAME?charset=utf8

5、监测 MySQL 数据库大小的的 sensor,可根据需要使用

- platform: sql
  db_url: mysql://user:[email protected]_IP:3306/information_schema
  queries:
    - name: hass_db_size
      query: "select concat(round(sum(DATA_LENGTH/1024/1024),0)) as db_size from TABLES where table_schema='HASS';"
      column: 'db_size'
      unit_of_measurement: MB

继续阅读

在 Entware 中安装设置 PostgreSQL

出于某些不愿多说的原因,把 Home Assistant 的数据库切换到了 MariaDB(MySQL),可能是设置问题,MariaDB 导致系统负载很高。还是换回 PostgreSQL(pgsql),系统负载低了很多。

安装 PostgreSQL

这次在 Entware 中安装,比较绿色。

opkg update
#安装pgsql
opkg install sudo pgsql-cli pgsql-cli-extra pgsql-server
source ~/.bashrc
#新建文件夹,赋权给postgres
mkdir -p /opt/var/pgsql/data
chown postgres /opt/var/pgsql/data
#初始化数据库
sudo -u postgres pg_ctl -D /opt/var/pgsql/data initdb
#启动pgsql
/opt/etc/init.d/S98postgresql start

不同于通过 apt-get 安装的,安装完成就已经可用了;在 Entware 中安装的软件,需要手动完成初始化等设置。

配置 PostgreSQL 远程连接

如果 PostgreSQL 安装在其他设备,或者要使用 Adminer 来远程管理,则需要远程连接。这时需要修改 /opt/var/pgsql/data 目录下的 pg_hba.conf 和 postgresql.conf。

1、pg_hba.conf,配置对数据库的访问权限。

# IPv4 local connections:
host  all    all    127.0.0.1/32     trust

不需要特别严格的权限控制时,可以直接将 IPv4 下面的设置修改为以下代码即可,可根据需要设置IP段

host  all    all    0.0.0.0/0        trust

2、postgresql.conf,配置PostgreSQL数据库服务器的相应的参数。PostgreSQL安装完成后,默认只接受来在本机localhost的连接请求。

定位到 #listen_addresses='localhost',修改为

listen_addresses = '*'

允许监听来自任何主机的连接请求。

3、重启 PostgreSQL 后生效,/opt/etc/init.d/S98postgresql restart

基于宝塔面板的 LNMP 服务器优化

宝塔面板实在是不可多得的国产优秀服务器管理面板。支持多种 Linux 系统和 Windows,防火墙安全管理,计划任务,文件管理,多版本 PHP 共存及切换,多版本 MySQL 切换,Apache 和 Nginx 切换,一键安装和更新软件。

宝塔面板

本文基于 LNMP(Linux+Nginx+Mysql+PHP) 下运行的 National lotterys 为例简单说明服务器性能优化。

一、MySQL 优化

根据服务器配置,安装 MySQL 5.5或者 MySQL 5.7,安装后的配置的是默认的,按需修改。

打开设置中的性能调整,根据内存大小选择优化方案。

另外,可以把默认存储引擎修改为 MyISAM,配置修改

default_storage_engine = MyISAM

二、PHP 优化

建议选择PHP 7.2,打开设置中的安装扩展,安装 opcache 和 memcached,扩展安装后会自动配置。

opcache 可以大幅提升 PHP 性能,飞一般的感觉,一定要安装。实测让 等待/Waiting (TTFB) 从平均 5 秒降低到 500 毫秒内,没有静态化缓存的页面也可以秒开了。

memcached 可以根据需要安装。 继续阅读

删除 MySQL 数据库中特定前缀的表

打开我的另一个网站的mysql数据库看了一下,一个库里竟然有750多张表格,关键是有很多是没有用的空表格。其实也没有太大的关系,主要就是管理起来不太方便。700多个表格不是一下子就可以打开phpMyAdmin管理的,会很慢甚至打不开。

于是找来下面的代码,删除特定前缀的表。

<?php
/*xuanfunction 删除数据库中有特定前缀的表
*$dbname 数据库名
*$tableflag 表前缀,这里标志出是哪一个用户,如表s00002_goods前缀为s00002
*/
function deldata($dbname,$tableflag){
$db_host   = 'localhost';
$db_port    = '3306';
$db_user    = 'user';
$db_pass    = 'pass';
$connect =mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($dbname);
$result = mysql_query("show table status from $dbname",$connect);
$data=mysql_fetch_array($result);
while($data=mysql_fetch_array($result)) {
$table=mysubstr($data[Name],"_");//截取当前表的下划线以前的字符串得到表前缀
if($table==$tableflag){ //判断截取的前缀是否等于自己需要的前缀$tableflag,如果是就删除该表
//测试之用
/*echo $data[Name];
echo "<br>";
echo $table;
echo "<br>";*/
mysql_query("drop table $data[Name]");
}
}
return true;
}
/*截取某个特定字符前的所有字符函数
*$str 为待截取字符串
*$flag 特定字符如“_”
*/
function mysubstr($str,$flag){
$pos=strpos($str,$flag);
return substr($str,0,$pos);
}
?>
<?php //test
$dbname="dbg0001";//数据库名
$tableflag="s000002";//要删除的表的前缀,有这个前缀的表才删除,标志出是哪一个用户
deldata($dbname,$tableflag);
//$test=mysubstr("s000002_account_log","_");
//echo $test;
?>