MariaDB setting

Installation

sudo apt update && sudo apt upgrade
sudo apt -y install software-properties-common
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
sudo add-apt-repository 'deb [arch=amd64] http://mariadb.mirror.globo.tech/repo/10.5/ubuntu focal main'

sudo apt install mariadb-server mariadb-client -y

## if you meet some error about libssl1.1, then
echo "deb http://security.ubuntu.com/ubuntu focal-security main" | sudo tee /etc/apt/sources.list.d/focal-security.list
sudo apt-get update
sudo apt-get install libssl1.1
## ------------------------------------

sudo mysql_secure_installation # y y y y y y

sudo mariadb -p

 

Remove

sudo apt-get purge mariadb-* mysql-*

 

In mariaDB,

use mysql;
SELECT user, host from user; # user 확인
UPDATE user SET plugin='mysql_native_password' WHERE User='root';
FLUSH PRIVILEGES;
exit

Restart MariaDB.

sudo service mysql restart

/lib/systemd/systemd-sysv-install enable mariadb ## 우분투를 시작하면 실행되도록

 

Configuration

 MariaDB [(none)]> show variables like '%timeout'; 
 
 MariaDB [(none)]> set global interactive_timeout = [설정값]
 MariaDB [(none)]> set global wait_timeout = [설정값]

 MariaDB [(none)]> set session interactive_timeout = [설정값]
 MariaDB [(none)]> set session wait_timeout = [설정값]
 
 /etc/mysql/mariadb.conf.d/50-server.cnf
 
 [mysqld]
 interactive_timeout = [설정값] //31536000
 wait_timeout = [설정값] //31536000

 

Python 에서 MariaDB에 table 넣기.

from sqlalchemy import create_engine
import pandas as pd

engine = create_engine("mysql+pymysql://root:passwd@localhost/dbname")
conn = engine.connect()

df = pd.read_table('OUT.txt.new',low_memory=False)

df.to_sql(name='OUT',con=engine, index=False)
conn.close()

 

Reset mariadb

sudo mysql_secure_installation

 

Logs

cat /var/log/mysql/error.log

 

Error

[ERROR] Aria engine is not enabled or did not start. The Aria engine must be enabled to continue as mysqld was configured with --with-aria-tmp-tables
$ mv /var/lib/mysql/aria_log_control /root/

 

비번없이 접속

ALTER USER root@localhost IDENTIFIED VIA unix_socket;