Linux
[root@SAMLab mysql]# cat /etc/my.cnf
Windows
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
C:\Users\teguh.triharto>cd C:\ProgramData\MySQL\MySQL Server 5.7
C:\ProgramData\MySQL\MySQL Server 5.7>dir
Volume in drive C has no label.
Volume Serial Number is 40FC-A9B1
Directory of C:\ProgramData\MySQL\MySQL Server 5.7
09/10/2015 11:54 AM <DIR> .
09/10/2015 11:54 AM <DIR> ..
07/12/2016 12:02 AM <DIR> data
07/15/2015 02:13 PM 50,331,648 ib_logfile0_2017
07/15/2015 02:13 PM 50,331,648 ib_logfile1_2017
09/10/2015 12:25 PM 14,121 my.ini
3 File(s) 100,677,417 bytes
3 Dir(s) 177,614,139,392 bytes free
C:\ProgramData\MySQL\MySQL Server 5.7>
2. additional sample tunning on my.ini or my.cnf and save
Simple tunning
transaction-isolation = READ-COMMITTED
max_connections=700
table_open_cache=500 ada yg 10000
innodb_log_file_size=900M
max_allowed_packet=256M
open-files-limit=10000
Advanced Tuning
| x | x | |||
| No | Before change my.ini or my.cnf | After change my.ini or my.cnf | ||
| 1 | [Mysql] | |||
| 2 | #[none] | no_auto_rehash | ||
| 3 | #[none] | max_allowed_packet=256M | ||
| 4 | [Mysqld] | |||
| 5 | #[none] | wait_timeout = 86400 | ||
| 6 | #[none] | transaction-isolation = READ-COMMITTED | ||
| 7 | #[none] | max_allowed_packet=256M | ||
| 8 | slow-query-log=1 | slow-query-log=0 | ||
| 9 | long_query_time=10 | long_query_time=0.5 | ||
| 10 | max_connections=700 | max_connections=1000 | ||
| 11 | query_cache_size=0 | query_cache_size=32M | ||
| 12 | table_open_cache=10000 | table_open_cache=2000 | ||
| 13 | #[none] | table_open_cache_instances=16 | ||
| 14 | tmp_table_size=398M | tmp_table_size=32M | ||
| 15 | thread_cache_size=10 | thread_cache_size=1000 | ||
| 16 | innodb_flush_log_at_trx_commit=1 | innodb_flush_log_at_trx_commit=0 | ||
| 17 | innodb_log_buffer_size=1M | innodb_log_buffer_size=64M | ||
| 18 | innodb_buffer_pool_size=8M | innodb_buffer_pool_size=16G | ||
| 19 | innodb_log_file_size=900M | innodb_log_file_size=256M | ||
| 20 | #[none] | innodb_strict_mode=ON | ||
| 21 | #[none] | innodb_log_files_in_group = 8 | ||
| 22 | #[none] | innodb_lock_wait_timeout = 7200 | ||
| 23 | join_buffer_size=256K | join_buffer_size=128K | ||
| 24 | open_files_limit=4161 | open_files_limit=10000 | ||
| 25 | query_cache_type=0 | query_cache_type=1 | ||
| x | Teguh Triharto | x | ||
3. restart mysql service
Linux
[root@TEGUHTH mysql]# service mysqld status
mysqld (pid 29142) is running...
[root@TEGUHTH mysql]# chkconfig mysqld on
[root@TEGUHTH mysql]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@TEGUHTH mysql]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@TEGUHTH mysql]#
Windows
C:\Users\teguh.triharto>net stop MySQL56
The MySQL57 service is stopping.
The MySQL57 service was stopped successfully.
C:\Users\teguh.triharto>net start MySQL56
The MySQL57 service is starting.
The MySQL57 service was started successfully.
C:\Users\teguh.triharto>
4. Login to make sure mysql is normal
# mysql -u root -p
[root@TEGUHTH mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.16 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> exit
Bye
[root@TEGUHTH mysql]#
5. Sample my.cnf on Linux
Sample Output my.cnf
[root@TeguhLab etc]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[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
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
transaction-isolation = READ-COMMITTED
max_connections=700
table_open_cache=500
innodb_log_file_size=900M
max_allowed_packet=256M
open-files-limit = 10000
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open-files-limit = 10000
[root@TeguhLab etc]#
Sample Output mysql.service
[root@TeguhLab etc]# cat /etc/systemd/system/mysql.service
#
# Simple MySQL systemd service file
#
# systemd supports lots of fancy features, look here (and linked docs) for a full list:
# http://www.freedesktop.org/software/systemd/man/systemd.exec.html
#
# Note: this file ( /usr/lib/systemd/system/mysql.service )
# will be overwritten on package upgrade, please copy the file to
#
# /etc/systemd/system/mysql.service
#
# to make needed changes.
#
# systemd-delta can be used to check differences between the two mysql.service files.
#
[Unit]
Description=MySQL Community Server
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
Alias=mysql.service
[Service]
User=mysql
Group=mysql
# Execute pre and post scripts as root
PermissionsStartOnly=true
# Needed to create system tables etc.
ExecStartPre=/usr/bin/mysql-systemd-start pre
# Start main service
ExecStart=/usr/bin/mysqld_safe
# Don't signal startup success before a ping works
ExecStartPost=/usr/bin/mysql-systemd-start post
# Give up if ping don't get an answer
TimeoutSec=600
LimitNOFILE=10000
Restart=always
PrivateTmp=false
[root@TeguhLab etc]#
No comments:
Post a Comment