MySQL設定確認

ストレージエンジン

create table t1 (a int, b int) engine innodb;create table t2 (a int, b int) engine myisam;

show engines;

use information_schema;
select table_name, engine from tables

MySQL5.7 yum CentOS6 install (失敗)

MySQL5.7 yum CentOS6.9

centos6
古いMySQL5.xの削除

cat /etc/redhat-release

arch

mysql --version

yum remove mysql*

---

*Download MySQL Yum Repository

https://dev.mysql.com/downloads/repo/yum/

rpm -ihv

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

*Download MySQL Yum Repository

cd /usr/local/src

wget https://dev.mysql.com/get/mysql57-community-release-el6-11.noarch.rpm

rpm -ihv mysql57-community-release-el6-11.noarch.rpm

---

*パッケージ情報確認
yum info mysql-community-server

---

*MySQLインストール
yum install mysql-community-server

---

*インストール確認
rpm -qa | grep mysql
mysql --version

---

*my.cnfの編集

cp -p /etc/my.cnf /etc/my00cnf.org
vi /etc/my.cnf

[client]
default-character-set = utf8mb4 <-- [client]セクションに追加する

[mysqld]
character-set-server=utf8mb4    <-- [mysqld]セクションに追加する

*起動
chkconfig mysqld on
service mysqld start

*初期パスワード確認
cat /var/log/mysqld.log | grep "temporary password"

---

*パスワード変更
validate_password_policyの初期値はMEDIUMで、パスワード設定に必要な条件は
MEDIUM ポリシーは、パスワードが最低 1 つの数値文字を含み、1 つの小文字および大文字を含み、1 つの特殊文字 (英数字以外) を含む必要があるという条件を追加します。

*mysql_secure_installation

[root@localhost ~]# cat /var/log/mysqld.log | grep "temporary password"
2017-06-12T12:14:10.568882Z 1 [Note] A temporary password is generated for root@localhost: 0;uE%(KBMiSD
[root@localhost ~]# mysql_secure_installation
mysql_secure_installation: [ERROR] unknown variable 'default-character-set=utf8mb4'

Securing the MySQL server deployment.

Enter password for user root:

The existing password for the user account root has expired. Please set a new password.

New password:

Re-enter new password:
 ... Failed! Error: Your password does not satisfy the current policy requirements

New password:

Re-enter new password:
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y

New password:

Re-enter new password:

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!
[root@localhost ~]#

---

*Login
mysql -u root -p
パスワード

centos7
sudo systemctl restart mysqld.service

---

Show variables like 'character_set%';

---

---

---

---

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4

[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
#
# 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
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

character-set-server = utf8mb4
default-storage-engine = InnoDB
explicit_defaults_for_timestamp = 1
default_password_lifetime = 0
lower-case-table-names = 2

    # max connections
    max_connections = 128
    # table_open_cache = (max_connections * tables used in one transaction) + alpha
    table_open_cache = 800
    # table_definition_cache = (all tables in mysql + max_connections) + alpha
    table_definition_cache = 400
    # open_files_limit = table_open_cache * 1.4
    open_files_limit = 1120
    
    # global buffer
    key_buffer_size = 16M
    query_cache_type = 0
    # innodb_buffer_pool_size = RAM for Mysql * 0.7
    innodb_buffer_pool_size = 2048M
    
    # thread buffer
    read_buffer_size = 256K
    read_rnd_buffer_size = 512K
    join_buffer_size = 256K
    sort_buffer_size = 512K
    
    # InnoDB
    innodb_file_per_table
    innodb_autoextend_increment = 64
    innodb_log_files_in_group = 2
    innodb_log_file_size = 64M
    innodb_log_buffer_size = 16M
    innodb_flush_log_at_trx_commit = 1
    innodb_flush_neighbors=0
    #innodb_flush_method=O_DIRECT
    innodb_thread_concurrency = 4
    innodb_commit_concurrency = 4


# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Mariadb cnf sample

/usr/share/mysql/my-huge.cnf 
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf 
/usr/share/mysql/my-small.cnf
/usr/share/mysql/my-large.cnf

cp /usr/share/mysql/my-lage.cnf /etc/my.cnf.d/server.cnf
cp /usr/share/mysql/my-huge.cnf /etc/my.cnf.d/server.cnf
cp /usr/share/mysql/my-large.cnf /etc/my.cnf.d/server.cnf
cp /usr/share/mysql/my-innodb-heavy-4G.cnf /etc/my.cnf.d/server.cnf

mriadb_cnf.zip

---