Category: MySQL

MySQL 8.0 Features

Document Store: Both relational and NoSQL applications can be developed in one DB.
Transactional data directory: implemented as a series of SQL tables stored in a single InnoDB tablespace.
SQL Role: Allows you to grant / remove privileges to groups of users, significantly reducing the burden of privilege management.
Default character set is utf8mb4: richer mobile applications and character set internalization
CTE(Common Table Expressions): Add WITH clause
Window function: Reduce code complexity and improve development productivity
Invisible Index: Better management of application upgrades and database changes running 24/7
Descending Index: Improves performance by eliminating the need to sort descending results
Json support: Add many functions such as JSON_TABLE function that receives JSON data and returns it as a relational table
Up to 2x faster: New benchmarks record up to 1.8 milion queries per second
OpenSSL: MySQL default TLS/SSL library
Performance schema: query execution time up to 30 times faster
Information schema: Query execution time is up to 100 times faster
GIS: supports SRS(Spatial Reference Systems)
Replication and InnoDB Cluster: Improve Scale Out of Large applilcations.

現状のバージョンを見てみると…

[vagrant@localhost ~]$ rpm -qa | grep -i mysql
php-mysqlnd-7.1.21-1.el6.remi.x86_64
mysql-community-common-5.6.41-2.el6.x86_64
mysql-community-devel-5.6.41-2.el6.x86_64
mysql-community-libs-5.6.41-2.el6.x86_64
mysql-connector-python-2.1.7-1.el6.x86_64
mysql-community-libs-compat-5.6.41-2.el6.x86_64
mysql-community-release-el6-5.noarch
mysql-community-client-5.6.41-2.el6.x86_64
mysql-utilities-1.6.5-1.el6.noarch
perl-DBD-MySQL-4.013-3.el6.x86_64
mysql-community-server-5.6.41-2.el6.x86_64
[vagrant@localhost ~]$ mysql –version
mysql Ver 14.14 Distrib 5.6.41, for Linux (x86_64) using EditLine wrapper
[vagrant@localhost ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.41 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> Ctrl-C — exit!
5.7か～

mysqlのアップグレードには
・mysql_upgradeを用いたアップグレード(インプレースアップグレード)
・レプリケーションを用いたアップグレード

CHARACTER_OCTET_LENGTH

CHARACTER_OCTET_LENGTH must be the same as CHARACTER_MAXIMUM_LENGTH except for multibyte character set.

mysql> select * from `information_schema`.`columns` where `table_schema` = “test”;
+—————+————–+————+————-+——————+—————-+————-+———–+————————–+————————+——————-+—————+——————–+——————–+—————–+————–+————+—————-+———————————+—————-+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT |
+—————+————–+————+————-+——————+—————-+————-+———–+————————–+————————+——————-+—————+——————–+——————–+—————–+————–+————+—————-+———————————+—————-+
| def | test | items | item_id | 1 | NULL | NO | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(11) | PRI | auto_increment | select,insert,update,references | |
| def | test | items | name | 2 | NULL | YES | text | 65535 | 65535 | NULL | NULL | NULL | utf8 | utf8_general_ci | text | | | select,insert,update,references | |
| def | test | items | price | 3 | NULL | YES | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(11) | | | select,insert,update,references | |
| def | test | name | name | 1 | NULL | YES | varchar | 255 | 765 | NULL | NULL | NULL | utf8 | utf8_general_ci | varchar(255) | | | select,insert,update,references | |
| def | test | name | password | 2 | NULL | YES | varchar | 255 | 765 | NULL | NULL | NULL | utf8 | utf8_general_ci | varchar(255) | | | select,insert,update,references | |
| def | test | news | id | 1 | NULL | NO | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(11) | PRI | auto_increment | select,insert,update,references | |
| def | test | news | content | 2 | NULL | YES | varchar | 100 | 300 | NULL | NULL | NULL | utf8 | utf8_general_ci | varchar(100) | | | select,insert,update,references | |
| def | test | user | id | 1 | NULL | NO | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(11) | MUL | auto_increment | select,insert,update,references | |
| def | test | user | name | 2 | NULL | YES | varchar | 20 | 60 | NULL | NULL | NULL | utf8 | utf8_general_ci | varchar(20) | | | select,insert,update,references | |
+—————+————–+————+————-+——————+—————-+————-+———–+————————–+————————+——————-+—————+——————–+——————–+—————–+————–+————+—————-+———————————+—————-+
9 rows in set (0.15 sec)

なんじゃーこりゃーーー

Config::Simple

Config::Simple – 簡単な設定ファイルクラス

use Config::Simple;
Config::Simple->import_from(‘app.ini’, \%Config);
$cfg = new Config::Simple(‘app.ini’);
$user = $cfg->param(‘User’);
%Config = $cfg->vars();
$cfg->param(‘User’, ‘sherzodR’);
$cfg->param(‘Users’, [‘sherzodR’, ‘geek’, ‘merlyn’]);
$cfg->param(-block=>’last-access’, -value=>{‘time’=>time()});
$mysql = $cfg->param(-block=>’mysql’);
$cfg->save();

mysqldumpでテーブルをバックアップ

command

$ mysqldump -u root -p db table --single-transaction > hoge.dump

test

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| items          |
| name           |
| news           |
| user           |
+----------------+
4 rows in set (0.00 sec)

[vagrant@localhost ~]$ mysqldump –single-transaction -u root -p test items > /tmp/dump/items.sql
Enter password:

ほーーーーーーー

複数テーブルの場合
[vagrant@localhost ~]$ mysqldump –single-transaction -u root -p test items name > /tmp/dump/some.dump
Enter password:

テーブルを複数並べればいいだけ。

オプション
–skip-lock-tables
–lock-tablesオプションを無効にする。–optが–lock-tablesを有効にするので、それを打ち消す為に使用する。

なるほどーーーーーー

mysqldump

Use mysqldump command to backup / resotre MySQL database. The mysqldump command is included with MySQL installation.

Will explain two backup method.
1. How to back up only specific databases
2. How to back up all databases

To dump backup data from a specific MySQL database, use the following command:

$ mysqldump --single-transaction -u username -p DBname > output destination file name

やってみます。
mysql> mysqldump –single-transaction -u root -p test > /tmp/dump/test.dump
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘mysqldump –single-transaction -u root -p test > /tmp/dump/test.dump’ at line 1

あれ？？？？？？　これ、mysqlにログインしなくていいのかな。
[vagrant@localhost ~]$ mysqldump –single-transaction -u root -p test > /tmp/dump/test.dump
Enter password:
[vagrant@localhost ~]$

ほう、そういうことか。。

次はテーブルのバックアップもやりたいね。

DB hash

With a hash table, the portion of the bucket stays on disk, halving the load on memory during the search.
Arrays need to use contiguous free space in memory. When reading a large table, it is very difficult to secure a sufficient continuous area. In the case of a hash table, you can choose the key you want to use.

indexのことか？しかし、ビューハッシュとはなんだ。。。

database backup

Database backup includes fullbackup, differential backup and incremental backup. This name is given in terms of “what data to back up”.

full backup
The easiest and the basis of all backups is a full backup. It literally backs up all the data held by the system at one point in time. Therefore, with this backup file, it is possible to recover all the data at that time.

This is a story, but if it is always possible to take a full backup during an active backup, the backup operation will be extreamly simple. Because, as mentioned above, data recovery will end easily if it is this.

Disadvantages of full backup
Cost1: It takes a long time to backup
The backup takes a long time to back up all the data in the system. Although some reductions can be made by excluding data that does not change, such as read-only files, it is necessary to include them in the backup target, since the core business data is usually change daily.

Cost: The load on resources is large
The fact that the data to be backed up is large means that a large number of read operations occur on the disk where the data is stored during the backup. Therefore, backup processing consumes a large amount of resources, including disk and server CPUs, and, if backup is performed via a network, network connections. Threfore, parallel execution of other processes during backup is not a pratical operation. A full backup consumes the most resources of the backup.

Cost: need to stop service
In general, shut down the software such as DBMS and stop the service before acquiring a full backup. This is because you must obtain backups while maintaining data integrity. However, there are not many systems that can stop service frequently.

Differential backup
Differential backup, as the name suggests, is a method of backup only the changes from the previous full backup.

Using differential backup increases the recovery procedure by one, but instead reduces the time required for backup because the data requried for daily backup is smaller. The impact on resources is also reduced accordingly.

Incremental backup
The thrd backup method is incremental backup. This is a smart version of the differential backup without any waste. Earlier, with differential backup files, there were many files that were unnecessary for recovery. That is because the information contained in these files is the same. If this redundancy is eliminated, the data to be backuped up will be less and the backup time will be shorter.

DBIで、mysqlにinsert

mysql> create table name(
-> name varchar(255),
-> password varchar(255)
-> );
Query OK, 0 rows affected (0.07 sec)

#!/usr/bin/perl --
use strict;
use utf8;
use warnings;
use CGI;
use DBI;

my $q = new CGI;
my $param1 = $q->param('name');
my $param2 = $q->param('password');

print "Content-type:text/html\n\n";
print "<html>\n";
print "<head></head>\n";
print "<form action=\"/cgi-bin/test2.cgi\">";
print "<label for=\"name\">お名前:</label>";
print "$param1<br>";
print "<label for=\"password\">パスワード:</label>";
print "$param2<br>";
print "<input type=\"submit\" value=\"送信\">";
print "</form>";
print "</html>";

my $user = 'root';
my $passwd = '';
my $db = DBI->connect('DBI:mysql:test:localhost', $user, $passwd);
my $sth = $db->prepare("INSERT INTO name(name, password) VALUES (?, ?)");
# $db->do("set names utf8");
$sth->execute($param1, $param2);

$sth->finish;
$db->disconnect;

print("finish\n");

mysql> select * from name;
Empty set (0.00 sec)

mysql> select * from name;
+——+———-+
| name | password |
+——+———-+
| hoge | hogehoge |
+——+———-+
1 row in set (0.00 sec)

ERROR 1068 (42000): Multiple primary key defined

ERROR 1068 (42000): Multiple primary key defined
複合primary keyで書こうとするとエラーになった。

create table hoge(
	submit_no int not null,
	seq_no int not null,
	report_file_name varchar(128),
	create_userid char(20) not null,
	create_date date not null,
	update_userid char(20),
	update_date date,
	primary key(submit_ms_no, seq_no)	
);

mysql> describe hoge;
+———————-+————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———————-+————–+——+—–+———+——-+
| submit_no | int(11) | NO | PRI | NULL | |
| seq_no | int(11) | NO | PRI | NULL | |
| report_img_file_name | varchar(128) | YES | | NULL | |
| create_userid | char(20) | NO | | NULL | |
| create_date | date | NO | | NULL | |
| update_userid | char(20) | YES | | NULL | |
| update_date | date | YES | | NULL | |
+———————-+————–+——+—–+———+——-+
7 rows in set (0.00 sec)

なるほど。。create文がそもそも違うのね。

MySQLで複数のPrimary Key

一つのテーブルで複数のprimary keyって設定できるの？

submit_noとseq_noでprimary keyを設定するとします。

create table rep_image_info(
	submit_no int not null primary key,
	seq_no int not null primary key,
	report varchar(128),
	create_user char(20),
	create_date date,
	update_user char(20),
	update_date date	
);

この場合、submit_noとseq_noの組み合わせに対して主キーを持つようです。なるほどにゃー
よって、二つでだけでなく、三つ、四つのprimary keyもあるわけですね。