DB : Mariadb , mysql
현재 테스트 환경 mysql 5.5
전체 데이터베이스 저장 디렉토리 /free/mysql_data
1. 현재 데이터베이스의 환경을 확인해 보자 / 기본 엔진이 MyISAM으로 되어 있으며 innodb_file_per_table 옵션은 off 되어 있는 상태입니다 (즉 ibdata1 파일에 데이터가 기록되는 구조)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> SELECT engine, support FROM information_schema.engines; +--------------------+---------+ | engine | support | +--------------------+---------+ | CSV | YES | | InnoDB | YES | | PERFORMANCE_SCHEMA | YES | | MRG_MYISAM | YES | | MyISAM | DEFAULT | | MEMORY | YES | +--------------------+---------+ mysql> show variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | OFF | +-----------------------+-------+ |
2. 현재 데이터베이스의 파일 확인 ( xinet 데이터베이스가 존재하며 해당 데이터는 ibdata1에 저장되어 있는 상태)
1 2 3 4 5 6 7 8 9 10 11 12 |
[root@localhost ~]# ls -lh /free/mysql_data 합계 45M -rw-rw---- 1 mysql mysql 5.0M 2019-12-26 13:54 ib_logfile0 -rw-rw---- 1 mysql mysql 5.0M 2019-12-26 13:54 ib_logfile1 -rw-rw---- 1 mysql mysql 34M 2019-12-26 13:54 ibdata1 -rw-r----- 1 mysql root 654K 2019-12-26 11:56 localhost.localdomain.err -rw-rw---- 1 mysql mysql 5 2019-12-26 11:56 localhost.localdomain.pid drwx------ 2 mysql mysql 4.0K 2016-01-04 20:06 mysql -rw-rw---- 1 mysql mysql 5.5K 2019-12-26 11:56 mysql-slow-queries.log drwx------ 2 mysql mysql 4.0K 2016-01-04 20:06 performance_schema drwx------ 2 mysql mysql 4.0K 2016-01-04 20:06 test drwx------ 2 mysql mysql 4.0K 2019-12-26 12:05 xinet |
3. 변경할 데이터베이스 파일 확인 ( xinet 데이터베이스)
1 2 3 4 5 6 7 8 9 10 |
[root@localhost ~]# ls -lh /free/mysql_data/xinet/ 합계 88K -rw-rw---- 1 mysql mysql 61 2016-01-27 15:17 db.opt -rw-rw---- 1 mysql mysql 8.9K 2019-12-26 12:05 main.frm -rw-rw---- 1 mysql mysql 8.9K 2019-12-26 12:05 main_server.frm -rw-rw---- 1 mysql mysql 8.5K 2019-12-26 12:05 main_server_list.frm -rw-rw---- 1 mysql mysql 8.9K 2019-12-26 12:05 main_server_result.frm -rw-rw---- 1 mysql mysql 8.9K 2019-12-26 12:05 vm.frm -rw-rw---- 1 mysql mysql 9.0K 2019-12-26 12:05 vm_list.frm -rw-rw---- 1 mysql mysql 9.1K 2019-12-26 12:05 vm_server_list.frm |
테이블 엔진상태 확인 기본 engine InnoDB 으로 되어 있다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
mysql> show create table vm_list\G; *************************** 1. row *************************** Table: vm_list Create Table: CREATE TABLE `vm_list` ( `m_idx` int(10) unsigned NOT NULL AUTO_INCREMENT, `stat` varchar(10) DEFAULT NULL, `m_name` varchar(50) DEFAULT NULL, `public_ip` varchar(18) DEFAULT NULL, `inside_ip` varchar(18) DEFAULT NULL, `c_host` varchar(10) DEFAULT NULL, `cpu` int(5) DEFAULT NULL, `mem` int(10) DEFAULT NULL, `replica` varchar(5) DEFAULT NULL, `backup` varchar(100) DEFAULT NULL, `remark` varchar(100) DEFAULT NULL, `vmname` varchar(50) DEFAULT NULL, `cpuper` int(10) DEFAULT NULL, `disk` int(10) DEFAULT NULL, `diskper` int(10) DEFAULT NULL, `inbound` float DEFAULT NULL, `outbound` float DEFAULT NULL, `m_time` timestamp NULL DEFAULT NULL, PRIMARY KEY (`m_idx`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) |
이제 InnoDB 으로 되어있는 데이터베이스를 myisam 로 변경하기 앞서 전체 데이터베이스를 백업 받자
1 2 |
[root@localhost ~]# mysqldump -u root -p --all-databases > all-databases-backup.sql Enter password: |
명령어로 일괄 ENGINE=MyISAM 변경 작업 ppassword 는 mysql의 root password를 입력해주면 된다
1 2 3 4 5 6 7 8 |
[root@localhost ~]# mysql -u root -ppassword -Bse 'SELECT CONCAT("ALTER TABLE ",table_schema,".",table_name," ENGINE=MyISAM;") FROM information_schema.tables WHERE table_schema NOT IN ("mysql","information_schema","performance_schema") AND Engine = "InnoDB";' | while read -r i; do echo $i; mysql -u root -ppassword -e "$i"; done | tee convert-to-myisam.log ALTER TABLE xinet.main ENGINE=MyISAM; ALTER TABLE xinet.main_server ENGINE=MyISAM; ALTER TABLE xinet.main_server_list ENGINE=MyISAM; ALTER TABLE xinet.main_server_result ENGINE=MyISAM; ALTER TABLE xinet.vm ENGINE=MyISAM; ALTER TABLE xinet.vm_list ENGINE=MyISAM; ALTER TABLE xinet.vm_server_list ENGINE=MyISAM; |
이제 변경이 되었으니 한번 데이터 저장 경로를 확인해보자
1 2 3 4 5 6 7 8 9 10 11 12 |
[root@localhost ~]# ls -lh /free/mysql_data 합계 45M -rw-rw---- 1 mysql mysql 5.0M 2019-12-26 14:01 ib_logfile0 -rw-rw---- 1 mysql mysql 5.0M 2019-12-26 13:54 ib_logfile1 -rw-rw---- 1 mysql mysql 34M 2019-12-26 14:01 ibdata1 -rw-r----- 1 mysql root 654K 2019-12-26 11:56 localhost.localdomain.err -rw-rw---- 1 mysql mysql 5 2019-12-26 11:56 localhost.localdomain.pid drwx------ 2 mysql mysql 4.0K 2016-01-04 20:06 mysql -rw-rw---- 1 mysql mysql 5.5K 2019-12-26 11:56 mysql-slow-queries.log drwx------ 2 mysql mysql 4.0K 2016-01-04 20:06 performance_schema drwx------ 2 mysql mysql 4.0K 2016-01-04 20:06 test drwx------ 2 mysql mysql 4.0K 2019-12-26 14:01 xinet |
ibdata1의 그대로 있고 데이터베이스의 파일들을 확인해보면 기존 frm 파일만 있던것이 MYI MYD 파일이 생성되었다
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
[root@localhost ~]# ls -lh /free/mysql_data/xinet/ 합계 12M -rw-rw---- 1 mysql mysql 61 2016-01-27 15:17 db.opt -rw-rw---- 1 mysql mysql 888 2019-12-26 14:01 main.MYD -rw-rw---- 1 mysql mysql 1.0K 2019-12-26 14:01 main.MYI -rw-rw---- 1 mysql mysql 8.9K 2019-12-26 14:01 main.frm -rw-rw---- 1 mysql mysql 428 2019-12-26 14:01 main_server.MYD -rw-rw---- 1 mysql mysql 1.0K 2019-12-26 14:01 main_server.MYI -rw-rw---- 1 mysql mysql 8.9K 2019-12-26 14:01 main_server.frm -rw-rw---- 1 mysql mysql 96 2019-12-26 14:01 main_server_list.MYD -rw-rw---- 1 mysql mysql 1.0K 2019-12-26 14:01 main_server_list.MYI -rw-rw---- 1 mysql mysql 8.5K 2019-12-26 14:01 main_server_list.frm -rw-rw---- 1 mysql mysql 340 2019-12-26 14:01 main_server_result.MYD -rw-rw---- 1 mysql mysql 2.0K 2019-12-26 14:01 main_server_result.MYI -rw-rw---- 1 mysql mysql 8.9K 2019-12-26 14:01 main_server_result.frm -rw-rw---- 1 mysql mysql 4.8K 2019-12-26 14:01 vm.MYD -rw-rw---- 1 mysql mysql 2.0K 2019-12-26 14:01 vm.MYI -rw-rw---- 1 mysql mysql 8.9K 2019-12-26 14:01 vm.frm -rw-rw---- 1 mysql mysql 0 2019-12-26 14:01 vm_list.MYD -rw-rw---- 1 mysql mysql 1.0K 2019-12-26 14:01 vm_list.MYI -rw-rw---- 1 mysql mysql 9.0K 2019-12-26 14:01 vm_list.frm -rw-rw---- 1 mysql mysql 11M 2019-12-26 14:01 vm_server_list.MYD -rw-rw---- 1 mysql mysql 592K 2019-12-26 14:01 vm_server_list.MYI -rw-rw---- 1 mysql mysql 9.1K 2019-12-26 14:01 vm_server_list.frm |
데이터베이스 들어가서 확인해 보자
1 2 3 4 5 6 7 8 |
mysql> use xinet; Database changed mysql> select count(*) from vm_server_list; +----------+ | count(*) | +----------+ | 58897 | +----------+ |
데이터가 정상적으로 존재한다
그럼 이제 ibdata1 파일은 필요가 없으므로 해당 파일을 삭제 후 mysql 재가동 해보자
단 혹시 모르는 전체 데이터 디록테로리르 백업해 두자
1 2 |
[root@localhost ~]# mysql.server stop Shutting down MySQL. [ OK ] |
1 |
[root@localhost ~]# cp -a /free/mysql_data /free/mysql_data_innodb |
1 2 3 4 5 6 7 8 9 10 11 |
[root@localhost mysql_data]# ll 합계 45740 -rw-rw---- 1 mysql mysql 5242880 2019-12-26 14:03 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 2019-12-26 13:54 ib_logfile1 -rw-rw---- 1 mysql mysql 35651584 2019-12-26 14:03 ibdata1 -rw-r----- 1 mysql root 671877 2019-12-26 14:03 localhost.localdomain.err drwx------ 2 mysql mysql 4096 2016-01-04 20:06 mysql -rw-rw---- 1 mysql mysql 5761 2019-12-26 14:03 mysql-slow-queries.log drwx------ 2 mysql mysql 4096 2016-01-04 20:06 performance_schema drwx------ 2 mysql mysql 4096 2016-01-04 20:06 test drwx------ 2 mysql mysql 4096 2019-12-26 14:01 xinet |
이제 더이상 ibdata1 파일은 필요 없으니 삭제해보자
1 |
[root@localhost mysql_data]# rm -f ibdata1 |
그리고 inndb에 사용하지 못하게 my.cnf에 옵션을 설정
1 2 3 |
[root@localhost ~]# vi /etc/my.cnf skip-innodb |
mysql 재시작
1 2 |
[root@localhost mysql_data]# mysql.server start Starting MySQL. [ OK ] |
데이터베이스 저장 디렉토리 파일 확인 ibdata1 파일은 없는상태
1 2 3 4 5 6 7 8 9 10 11 |
[root@localhost mysql_data]# ls -lh /free/mysql_data 합계 21M -rw-rw---- 1 mysql mysql 5.0M 2019-12-26 14:03 ib_logfile0 -rw-rw---- 1 mysql mysql 5.0M 2019-12-26 13:54 ib_logfile1 -rw-r----- 1 mysql root 659K 2019-12-26 14:04 localhost.localdomain.err -rw-rw---- 1 mysql mysql 5 2019-12-26 14:04 localhost.localdomain.pid drwx------ 2 mysql mysql 4.0K 2016-01-04 20:06 mysql -rw-rw---- 1 mysql mysql 5.8K 2019-12-26 14:04 mysql-slow-queries.log drwx------ 2 mysql mysql 4.0K 2016-01-04 20:06 performance_schema drwx------ 2 mysql mysql 4.0K 2016-01-04 20:06 test drwx------ 2 mysql mysql 4.0K 2019-12-26 14:01 xinet |
데이터베이스 들어가서 자료 확인
1 2 3 4 5 6 7 8 9 |
mysql> use xinet; Database changed mysql> select count(*) from vm_server_list; +----------+ | count(*) | +----------+ | 58897 | +----------+ 1 row in set (0.00 sec) |
이렇게 기존 innodb로 되어있던 데이터베이스를 myisam으로 변경 완료
데이터베이스 xinet 파일 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
[root@localhost ~]# ls -lh /free/mysql_data/xinet/ 합계 12M -rw-rw---- 1 mysql mysql 61 2016-01-27 15:17 db.opt -rw-rw---- 1 mysql mysql 888 2019-12-26 14:01 main.MYD -rw-rw---- 1 mysql mysql 1.0K 2019-12-26 14:01 main.MYI -rw-rw---- 1 mysql mysql 8.9K 2019-12-26 14:01 main.frm -rw-rw---- 1 mysql mysql 428 2019-12-26 14:01 main_server.MYD -rw-rw---- 1 mysql mysql 1.0K 2019-12-26 14:01 main_server.MYI -rw-rw---- 1 mysql mysql 8.9K 2019-12-26 14:01 main_server.frm -rw-rw---- 1 mysql mysql 96 2019-12-26 14:01 main_server_list.MYD -rw-rw---- 1 mysql mysql 1.0K 2019-12-26 14:01 main_server_list.MYI -rw-rw---- 1 mysql mysql 8.5K 2019-12-26 14:01 main_server_list.frm -rw-rw---- 1 mysql mysql 340 2019-12-26 14:01 main_server_result.MYD -rw-rw---- 1 mysql mysql 2.0K 2019-12-26 14:01 main_server_result.MYI -rw-rw---- 1 mysql mysql 8.9K 2019-12-26 14:01 main_server_result.frm -rw-rw---- 1 mysql mysql 4.8K 2019-12-26 14:01 vm.MYD -rw-rw---- 1 mysql mysql 2.0K 2019-12-26 14:01 vm.MYI -rw-rw---- 1 mysql mysql 8.9K 2019-12-26 14:01 vm.frm -rw-rw---- 1 mysql mysql 0 2019-12-26 14:01 vm_list.MYD -rw-rw---- 1 mysql mysql 1.0K 2019-12-26 14:01 vm_list.MYI -rw-rw---- 1 mysql mysql 9.0K 2019-12-26 14:01 vm_list.frm -rw-rw---- 1 mysql mysql 11M 2019-12-26 14:01 vm_server_list.MYD -rw-rw---- 1 mysql mysql 592K 2019-12-26 14:01 vm_server_list.MYI -rw-rw---- 1 mysql mysql 9.1K 2019-12-26 14:01 vm_server_list.frm |