partition by range innodb discard tablespace

https://bobcares.com/blog/database-crash-rescue-how-we-re-built-an-innodb-mysql-database-when-ibdata1-file-was-corrupted/

https://www.percona.com/blog/2014/10/14/recover-orphaned-innodb-partition-tablespaces-in-mysql/

ooo

apt-get install mysql-utilities

查table statement
mysqlfrm --diagnostic xxxxoooo.frm > t.sql

remove t.sql  not sql statme

mysql -u root -pxxxooo database < t.sql

percona stand my.cnf

https://tools.percona.com/wizard/


# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
# Configuration name server generated for at 2017-04-26 15:24:40

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 128M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 2G

# LOGGING #
log_bin                      = /var/log/mysql/mysql.log
log-error                      = /var/log/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/log/mysql/mysql-slow.log

install mariadb finish give root all connect and grant

create user 'root'@'%' identified by 'password'; 

grant ALL PRIVILEGES on *.* to 'root'@'%' with grant option;

flush privileges;

if error  have root@localhost

delete from mysql.user where user='root';
flush privileges;
create user 'root'@'%' identified by 'password';
grant ALL PRIVILEGES on *.* to 'root'@'%' with grant option;

select user, host from user;
show grants for 'root'@'%';

FLUSH PRIVILEGES;



=====restore root privileges=====

http://stackoverflow.com/questions/1709078/how-can-i-restore-the-mysql-root-user-s-full-privileges



UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';




FLUSH PRIVILEGES;




create user 'root'@'localhost' identified by 'password';

create user 'root'@'%' identified by 'password';




GRANT ALL ON *.* TO 'root'@'localhost';

GRANT ALL ON *.* TO 'root'@'%';

netdata io

https://my-netdata.io/

mariadb tunning

character-set-server = utf8mb4

#tunning
skip_name_resolve = 1


open_files_limit  = 65535
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
#table_open_cache_instances = 64  #MySQL 5.6
sort_buffer_size = 4M
join_buffer_size = 4M
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
#master_info_repository = TABLE  #MySQL 5.6
#relay_log_info_repository = TABLE  #MySQL 5.6
#gtid_mode = on
#enforce_gtid_consistency = 1
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
lock_wait_timeout = 3600
#explicit_defaults_for_timestamp = 1  #MySQL 5.6

innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30
transaction_isolation = REPEATABLE-READ
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2867M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
#innodb_data_file_path = ibdata1:1G:autoextend
#正確做法，如果原本己經有了  http://www.cnblogs.com/ivictor/p/5340822.html
#innodb_data_file_path = ibdata1:原始大小;ibdata2:1G:autoextend

innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
#innodb_max_undo_log_size = 4G

innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
#innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
#ineinternal_tmp_disk_storage_engine = InnoDB
innodb_status_file = 1
innodb_status_output = 1
innodb_status_output_locks = 1
innodb_stats_on_metadata = 0

#performance_schema
performance_schema = 1
performance_schema_instrument = '%=on'

#innodb monitor  5.7後禁用
#innodb_monitor_enable="module_innodb"
#innodb_monitor_enable="module_server"
#innodb_monitor_enable="module_dml"
#innodb_monitor_enable="module_ddl"
#innodb_monitor_enable="module_trx"
#innodb_monitor_enable="module_os"
#innodb_monitor_enable="module_purge"
#innodb_monitor_enable="module_log"
#innodb_monitor_enable="module_lock"
#innodb_monitor_enable="module_buffer"
#innodb_monitor_enable="module_index"
#innodb_monitor_enable="module_ibuf_system"
#innodb_monitor_enable="module_buffer_page"
#innodb_monitor_enable="module_adaptive_hash"



key_buffer_size = 32M #16M
max_allowed_packet = 32M #16M
thread_stack = 512K #192K
thread_cache_size       = 150 #8


myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10



query_cache_type = 0
query_cache_limit = 1M
query_cache_size        = 0 #16M




slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 1
log_slow_rate_limit = 1000
log_slow_verbosity = query_plan




server-id               = 3306
log_bin = /var/log/mysql/mysql-bin.log
sync_binlog = 1
expire_logs_days = 7
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
binlog_format = row
relay_log_recovery = 1
relay-log-purge = 1


程序猿都该知道的MySQL秘籍 - 叶金荣@-----
thread_handling=pool-of-threads

http://www.cnblogs.com/nocode/archive/2013/05/25/3098317.html


-----
檔案系統 xfs > ext4 >ext 2 or 3


innodb_buffer_pool_size  約物理內存的50%~70%

innodb_data_file_path  分始化大小至少1G

innodb_log_file_size 1G以上


innodb_flush_log_at_trx_commit  0=>最快、數據最不安全， 1=>最慢最安全， 2=>折中

innodb_max_dirty_pages_pct  25~50(25%~50%)為宜

innodb_io_capacity  HDD=>1,000左右，SSD=>10,000左右，PCIe SSD=>20,000以上
**kernel io scheduler  => dedline    SSD=>noop  


key_buffer_size  32M以下

sync_binlog  0=>最快，數據最不安全，系統自決定刷新binlog的頻率；1=>最慢最安全，每個event刷新一次；N=>每N個事務刷一次binglog

long_query_time  建議設置小于0.5秒

open_files_limit & innodb_open_files  建議65535


max_connections 突發最大連接數的80%為宜，過大容易導致全部卡死

query_cache_size=0 & query_cache_type=0 關閉


-----
http://imysql.com/2016/03/11/mysql-faq-why-ibdata1-size-growup.shtml

ibdata1 文件儲存了什麼內容？

Data dictionary

Double write bufer

Insert buffer

Rollback segments

UNDO space

Foreign key constraint system tables


但當 啟用獨立表空間，ibdata1只存放系統數據

innodb_file_per_table = 1



-----
連接數過高

大部份的不正確做法：殺掉多餘的連接，加人連接數

正確做法：

．限制連接數： max_user_connections



-----
http://imysql.com/2016/01/13/mysql-optimization-case-howto-find-performance-bottleneck.shtml

檢查指令

vmstat -S m 1

sar -d 1    or   w

iotop



 mysqladmin pr|grep -v Sleep

-----
http://imysql.com/2015/12/30/mysql-faq-howto-stat-response-time-shtml.shtml



http://imysql.com/2015/11/04/mysql-faq-some-important-in-explain.shtml

explain


-----
http://imysql.com/category/%E6%95%B0%E6%8D%AE%E5%BA%93/page/3



将操作日志记入syslog并且发送到远程log server上，坚决不能只存储在本地；

设置密码安全策略，MD5之后再作为正式密码，32位长度的安全程度够高吧；

应用账号只赋予SELECT、UPDATE、INSERT权限，取消DELETE权限。把需要DELETE权限的逻辑改成用UPDATE实现，避免被物理删除；

需要真正删除时，交由DBA先备份后再物理删除；

-----
https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/


-----
Monitor MySQL Performance
https://www.tecmint.com/mysql-performance-monitoring/

• Mytop

https://www.packtpub.com/mapt/book/application_development/9781783981601/2/ch02lvl1sec25/mytop

http://15jb.net/mariadb%E6%80%A7%E8%83%BD%E8%AA%BF%E5%84%AA%E5%B7%A5%E5%85%B7mytop%E7%9A%84%E4%BD%BF%E7%94%A8%E8%A9%B3%E8%A7%A3

• innotop

• mysqlsla

https://www.packtpub.com/mapt/book/application_development/9781783981601/2/ch02lvl1sec27/mysqlsla

• mysqladmin status

• MySQL性能查询脚本

http://www.cnblogs.com/ivictor/p/5345217.html


==============SQL===============

SHOW GLOBAL VARIABLES where
Variable_Name in (
'table_open_cache',              
'table_definition_cache',        
                                 
'sort_buffer_size',              
'join_buffer_size',              
'interactive_timeout',            
'wait_timeout',                
'tmp_table_size',
'max_heap_table_size',            
'read_buffer_size',              
'read_rnd_buffer_size',          
'bulk_insert_buffer_size',        
'lock_wait_timeout',              
                                 
'innodb_thread_concurrency',      
'innodb_sync_spin_loops',      
'innodb_spin_wait_delay',      
'transaction_isolation',            
'innodb_additional_mem_pool_size',  
'innodb_buffer_pool_size',          
'innodb_buffer_pool_instances',    
'innodb_buffer_pool_load_at_startup',
'innodb_buffer_pool_dump_at_shutdown',
'innodb_flush_log_at_trx_commit',  
'innodb_log_buffer_size',          
'innodb_log_file_size',            
'innodb_log_files_in_group',        
                                 
'innodb_write_io_threads',          
'innodb_read_io_threads',          
'innodb_purge_threads',            
'innodb_open_files',                
'innodb_max_dirty_pages_pct',      
'innodb_flush_method',              
'innodb_lru_scan_depth',
'innodb_checksum_algorithm',        
'innodb_lock_wait_timeout',        
'innodb_rollback_on_timeout',
'innodb_print_all_deadlocks',
'innodb_file_per_table',            
'innodb_online_alter_log_max_size',
'innodb_status_file',              
'innodb_status_output',
'innodb_status_output_locks',      
'innodb_stats_on_metadata',        
                                 
'performance_schema',              
'performance_schema_instrument',



'key_buffer_size',    
'max_allowed_packet',
'thread_stack',  
'thread_cache_size',  
                   
                   
'query_cache_type',
'query_cache_limit',
'query_cache_size',  
                   
'server-id',          
'log_bin',      
'sync_binlog',        
'expire_logs_days',
'binlog_cache_size',
'max_binlog_cache_size',
'max_binlog_size',    
'binlog_format',      
'relay_log_recovery',
'relay-log-purge'    

)