Перевод базы в InnoDB
Перед тем как начинать, советую сделать бекап текущих данных в mysql:
mysqldump --routines --extended-insert -uroot -pPASSWORD DBNAME| gzip > /home/sqlback.sql.gz
Теперь приступим: перейдем в каталог /opt/ выполнив:
cd /opt/
создадим скрипты для перевода базы сначала в MyISAM:
nano mikbill-to-MyISAM.sh
#!/bin/bash USER="root" PASSWORD="" MYSQL="mysql -u$USER -p$PASSWORD " echo "SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;') FROM information_schema.tables WHERE engine = 'InnoDB' AND table_schema NOT IN ('information_schema','mysql','performance_schema');" | $MYSQL > convert.sql sed '1d' ./convert.sql > ./convert2.sql $MYSQL < ./convert2.sql
Замените логин и пароль на свои!
аналогично для перевода базы в InnoDB:
nano mikbill-to-InnoDB.sh
#!/bin/bash USER="root" PASSWORD="" MYSQL="mysql -u$USER -p$PASSWORD " echo "SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') FROM information_schema.tables WHERE engine = 'MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema');" | $MYSQL > convert3.sql sed '1d' ./convert3.sql > ./convert4.sql $MYSQL < ./convert4.sql
Замените логин и пароль на свои!
добавим права запуска на эти файлы:
chmod +x mikbill-to-MyISAM.sh chmod +x mikbill-to-InnoDB.sh
Начнем перевод базы в MyISAM. запустите скрипт mikbill-to-MyISAM.sh командой:
./mikbill-to-MyISAM.sh
После выполнения скрипта, запустите его повторно.
Теперь нужно заменить файл настроек my.cnf сначала сохраним прежний файл командой:
на Centos:
cp /etc/my.cnf /etc/my.cnf_old
на Debian:
cp /etc/mysql/my.cnf /etc/mysql/my.cnf_old
и заменим содержимое my.cnf этим:
Centos:
[client] port = 3306 socket=/var/lib/mysql/mysql.sock [mysql] loose-local-infile=1 [mysqld_safe] err-log = /var/log/mysql/mysql.err [mysqld] user = mysql port = 3306 socket = /var/lib/mysql/mysql.sock pid-file = /var/run/mysqld/mysqld.pid log-error = /var/log/mysql/mysql.err basedir = /usr datadir = /var/lib/mysql skip-external-locking symbolic-links=0 skip-name-resolve #system language = /usr/share/mysql/english loose-local-infile=1 query_cache_size = 256M query_cache_limit = 256M join_buffer_size = 256M max_join_size=256M max_connect_errors = 1K max_allowed_packet=16M table_open_cache = 1k table_cache = 1k max_heap_table_size = 1024M tmp_table_size = 1024M interactive_timeout = 420 wait_timeout = 420 connect_timeout = 420 thread_cache_size = 32 max_connections = 64 #Myisam key_buffer_size = 8M sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 2M #ниже включается бинари лог, разкоментируйте следующие 2 строки чтобы включить #server-id=1 #log-bin=mysqld-bin tmpdir = /tmp/ #innodb innodb_file_per_table innodb_flush_method=O_DIRECT innodb_buffer_pool_size = 1G innodb_additional_mem_pool_size = 4M innodb_data_home_dir = /var/lib/mysql/ innodb_log_group_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_file_size = 256M innodb_log_buffer_size = 8M innodb_log_files_in_group = 2 innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 70 [mysqldump] quick max_allowed_packet = 16M [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer_size = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
Debian:
[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysql] loose-local-infile=1 [mysqld_safe] err-log = /var/log/mysql.err socket = /var/run/mysqld/mysqld.sock [mysqld] user = mysql port = 3306 socket = /var/run/mysqld/mysqld.sock pid-file = /var/run/mysqld/mysqld.pid log-error = /var/log/mysql.err basedir = /usr tmpdir = /tmp/ datadir = /var/lib/mysql skip-external-locking symbolic-links=0 skip-name-resolve lc-messages-dir = /usr/share/mysql #system language = /usr/share/mysql/english loose-local-infile=1 query_cache_size = 256M query_cache_limit = 256M join_buffer_size = 256M max_join_size=256M max_connect_errors = 1K max_allowed_packet=16M table_open_cache = 1k table_cache = 1k max_heap_table_size = 1024M tmp_table_size = 1024M interactive_timeout = 420 wait_timeout = 420 connect_timeout = 420 thread_cache_size = 32 max_connections = 64 #Myisam key_buffer_size = 8M sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 2M #ниже включается бинари лог, разкоментируйте следующие 2 строки чтобы включить #server-id=1 #log-bin=mysqld-bin #innodb innodb_file_per_table innodb_flush_method=O_DIRECT innodb_buffer_pool_size = 1G innodb_additional_mem_pool_size = 4M innodb_data_home_dir = /var/lib/mysql/ innodb_log_group_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_file_size = 256M innodb_log_buffer_size = 8M innodb_log_files_in_group = 2 innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 70 [mysqldump] quick max_allowed_packet = 16M [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer_size = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [mysqld_safe] log-error=/var/log/mysql.log pid-file=/var/run/mysqld/mysqld.pid
Обратите внимание на :
# Cpu count * 2 thread_concurrency = 4 # Cpu count * 2 innodb_thread_concurrency = 4 # 1/3 or 1/4 RAM, 4G max. innodb_buffer_pool_size = 512M
Их нужно выставлять исходя из параметров системы.
Теперь выключим Mysql командой:
/etc/init.d/mysql stop
и перейдем в каталог с файлами базы:
cd /var/lib/mysql
не будем удалять старые файлы, а просто переместим их:
mv ibdata1 ibdata1_off mv ib_logfile0 ib_logfile0_off mv ib_logfile1 ib_logfile1_off
И запустим Mysql командой:
/etc/init.d/mysql start
после переходим в каталог /opt/ командой:
cd /opt/
и запускам скрипт перевода базы в InnoDB:
./mikbill-to-InnoDB.sh
После выполнения скрипта, запустите его повторно.
PS: Определить оптимальный размер innodb_buffer_pool_size можно SQL запросом:
SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)), SUBSTR(' KMGT',pw+1,1)) Recommended_InnoDB_Buffer_Pool_Size FROM ( SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw FROM ( SELECT SUM(data_length+index_length)*1.1*growth RIBPS FROM information_schema.tables AAA, (SELECT 1 growth) BBB WHERE ENGINE='InnoDB' ) AA) A;