[MySQL优化]为MySQL数据文件ibdata1瘦身
系统环境 发现问题 解决问题 1. 系统环境 Linux Ubuntu 13.04 64bit server ~ uname -aLinux d2 3.8.0-21-generic #32-Ubuntu SMP Tue May 14 22:16:46 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux~ cat /etc/issueUbuntu 13.04 \n \l MySQL: 5.5.31-0ubuntu0.
- 系统环境
- 发现问题
- 解决问题
1. 系统环境
Linux Ubuntu 13.04 64bit server
<code>~ uname -a Linux d2 3.8.0-21-generic #32-Ubuntu SMP Tue May 14 22:16:46 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux ~ cat /etc/issue Ubuntu 13.04 \n \l </code>
MySQL: 5.5.31-0ubuntu0.13.04.1
<code>~ mysql --version mysql Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (x86_64) using readline 6.2 </code>
硬盘：36G+4G+4G+36G
<code>~ df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/server3--vg-root 36G 31G 3.2G 91% / none 4.0K 0 4.0K 0% /sys/fs/cgroup udev 4.1G 1.1G 3.0G 26% /dev tmpfs 824M 280K 823M 1% /run none 5.0M 0 5.0M 0% /run/lock none 4.1G 3.4G 729M 83% /run/shm none 100M 0 100M 0% /run/user /dev/vda1 228M 30M 187M 14% /boot 192.168.1.10:/home/amg/data 36G 13G 21G 39% /home/amg/data </code>
MySQL的ibdata1占用空间:20G
<code>~ cd /var/lib/mysql ~ ls -l drwxr-xr-x 2 mysql mysql 4096 Aug 2 19:38 CB drwxr-xr-x 2 mysql mysql 4096 Jun 24 23:08 conan drwxr-xr-x 2 mysql mysql 4096 Jun 2 00:52 dbwordpress -rwxr-xr-x 1 root root 0 May 23 00:48 debian-5.5.flag -rwxr-xr-x 1 mysql mysql 20101201920 Aug 2 20:08 ibdata1 -rwxr-xr-x 1 mysql mysql 5242880 Aug 2 20:08 ib_logfile0 -rwxr-xr-x 1 mysql mysql 5242880 Aug 2 19:38 ib_logfile1 drwxr-xr-x 2 mysql mysql 4096 Jun 26 09:03 Macro drwxr-xr-x 2 mysql root 4096 May 23 00:48 mysql -rwxr-xr-x 1 root root 6 May 23 00:48 mysql_upgrade_info drwxr-xr-x 2 mysql mysql 4096 May 23 00:48 performance_schema drwxr-xr-x 2 mysql mysql 4096 May 23 00:53 phpmyadmin drwxr-xr-x 2 mysql root 4096 May 23 00:48 test drwxr-xr-x 2 mysql mysql 4096 Jul 22 14:09 TF drwxr-xr-x 2 mysql mysql 4096 Jun 2 01:04 wordpress </code>
业务数据表
<code>mysql> show tables; +-----------------+ | Tables_in_CB | +-----------------+ | NSpremium | | cb_hft | | cb_hft_20130801 | | cb_hft_20130802 | +-----------------+ 4 rows in set (0.00 sec) </code>
2. 发现问题
ibdata1单个文件占用20G大小。
1. MySQL默认设置，没有按表空间分离数据，所有的表的数据都被放到ibdata1文件中。
2. 业务操作，每天会产生一张表cb_hft，晚上的时候对表进行重命名。
<code>RENAME TABLE cb_hft TO cb_hft_20130801; create table cb_hft like cb_hft_20130801; </code>
3. 每周会把数据导出，同时drop表。但drop后，ibdata1不会减少，随着数据的积累ibdata1越来越大，根空间已经不够用了。
3. 解决问题
1). 导出数据
现在数据库中，有两个数据表，cb_hft_20130801,cb_hft_20130802，分别导出到/run/shm, /dev
<code>~ cd /dev ~ mysqldump -uroot -p CB cb_hft_20130802 > export_cb_hft_20130802.sql ~ cd /run/shm ~ mysqldump -uroot -p CB cb_hft_20130801 > export_cb_hft_20130801.sql ~ df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/server3--vg-root 36G 31G 3.2G 91% / none 4.0K 0 4.0K 0% /sys/fs/cgroup udev 4.1G 3.7G 368M 92% /dev tmpfs 824M 280K 823M 1% /run none 5.0M 0 5.0M 0% /run/lock none 4.1G 3.4G 729M 83% /run/shm none 100M 0 100M 0% /run/user /dev/vda1 228M 30M 187M 14% /boot 192.168.1.10:/home/amg/data 36G 13G 21G 39% /home/amg/data </code>
两张表分别占了，3.4G，3.7G。
登陆mysql删除CB数据库
<code>~ mysql -uroot -p ~ drop database CB </code>
导出其他数据库数据
<code>~ cd /run/shm ~ mysqldump -uroot -p -R -q --all-databases > others.sql </code>
2). 修改配置文件/etc/mysql/my.cnf
对每张表使用单独的数据文件存储innodb_file_per_table
停止mysql服务器
<code>~ sudo /etc/init.d/mysql stop #清空所有数据文件 ~ sudo rm -rf /var/lib/mysql/* </code>
修改配置文件
<code>~ sudo vi /etc/mysql/my.cnf [mysqld] innodb_file_per_table </code>
重新构建数据库实例
<code>~ /usr/bin/mysql_install_db ~ ls /var/lib/mysql mysql performance_schema test #启动MySQL ~ sudo /etc/init.d/mysql start </code>
3). 恢复其他数据库
<code>~ mysql show databases; +--------------------+ | Database | +--------------------+ | information_schema | | Macro | | TF | | conan | | dbwordpress | | mysql | | performance_schema | | phpmyadmin | | test | | wordpress | +--------------------+ 10 rows in set (0.01 sec) #查看ibdata1大小 ~ ls -l /var/lib/mysql drwx------ 2 mysql mysql 4096 Aug 2 21:33 CB drwx------ 2 mysql mysql 4096 Aug 2 21:23 conan drwx------ 2 mysql mysql 4096 Aug 2 21:23 dbwordpress -rw-rw---- 1 mysql mysql 18874368 Aug 2 21:34 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Aug 2 21:34 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Aug 2 21:34 ib_logfile1 drwx------ 2 mysql mysql 4096 Aug 2 21:23 Macro drwx------ 2 mysql root 4096 Aug 2 21:23 mysql drwx------ 2 mysql mysql 4096 Aug 2 21:19 performance_schema drwx------ 2 mysql mysql 4096 Aug 2 21:23 phpmyadmin drwx------ 2 mysql root 4096 Aug 2 21:19 test drwx------ 2 mysql mysql 4096 Aug 2 21:23 TF drwx------ 2 mysql mysql 4096 Aug 2 21:23 wordpress </code>
4). 重置root密码
<code>mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
mysql> FLUSH PRIVILEGES;
</code>5). 恢复CB数据库
<code>mysql> create database CB; Query OK, 1 row affected (0.00 sec) ~ mysql --database CB </code>
刚才设置的innodb_file_per_table参数已经起作用了，当我们再导出表drop后，对应的数据文件idb就会被删除，系统硬盘空间使用就会在正常值范围内。
查看表数据
<code>mysql> show tables; +-----------------+ | Tables_in_CB | +-----------------+ | cb_hft_20130801 | | cb_hft_20130802 | +-----------------+ 2 rows in set (0.00 sec) mysql> select count(1) from cb_hft_20130801; +----------+ | count(1) | +----------+ | 21063172 | +----------+ 1 row in set (1 min 1.46 sec) #删除表 ~ drop table cb_hft_20130801; #查看数据文件 ~ ls -l /var/lib/mysql/CB -rw-rw---- 1 mysql mysql 9928 Aug 2 22:09 cb_hft_20130802.frm -rw-rw---- 1 mysql mysql 7805599744 Aug 2 22:38 cb_hft_20130802.ibd -rw-rw---- 1 mysql mysql 61 Aug 2 21:30 db.opt -rw-rw---- 1 mysql mysql 9274 Aug 2 22:52 NSpremium.frm -rw-rw---- 1 mysql mysql 98304 Aug 2 22:53 NSpremium.ibd </code>
drop后，数据就一起被删除了。
经过对MySQL的调优，ibdata1已经被瘦身！数据库又可以继续正常的稳定的工作了。
http://blog.fens.me/mysql-ibdata1/
Outils d'IA chauds
Undresser.AI Undress
Application basée sur l'IA pour créer des photos de nu réalistes
AI Clothes Remover
Outil d'IA en ligne pour supprimer les vêtements des photos.
Undress AI Tool
Images de déshabillage gratuites
Clothoff.io
Dissolvant de vêtements AI
Video Face Swap
Échangez les visages dans n'importe quelle vidéo sans effort grâce à notre outil d'échange de visage AI entièrement gratuit !
Article chaud
Outils chauds
Bloc-notes++7.3.1
Éditeur de code facile à utiliser et gratuit
SublimeText3 version chinoise
Version chinoise, très simple à utiliser
Envoyer Studio 13.0.1
Puissant environnement de développement intégré PHP
Dreamweaver CS6
Outils de développement Web visuel
SublimeText3 version Mac
Logiciel d'édition de code au niveau de Dieu (SublimeText3)
Sujets chauds
Comment ouvrir phpmyadmin
Apr 10, 2025 pm 10:51 PM
Vous pouvez ouvrir PHPMYADMIN via les étapes suivantes: 1. Connectez-vous au panneau de configuration du site Web; 2. Trouvez et cliquez sur l'icône PHPMYADMIN; 3. Entrez les informations d'identification MySQL; 4. Cliquez sur "Connexion".
MySQL: une introduction à la base de données la plus populaire au monde
Apr 12, 2025 am 12:18 AM
MySQL est un système de gestion de la base de données relationnel open source, principalement utilisé pour stocker et récupérer les données rapidement et de manière fiable. Son principe de travail comprend les demandes des clients, la résolution de requête, l'exécution des requêtes et les résultats de retour. Des exemples d'utilisation comprennent la création de tables, l'insertion et la question des données et les fonctionnalités avancées telles que les opérations de jointure. Les erreurs communes impliquent la syntaxe SQL, les types de données et les autorisations, et les suggestions d'optimisation incluent l'utilisation d'index, les requêtes optimisées et la partition de tables.
Place de MySQL: bases de données et programmation
Apr 13, 2025 am 12:18 AM
La position de MySQL dans les bases de données et la programmation est très importante. Il s'agit d'un système de gestion de base de données relationnel open source qui est largement utilisé dans divers scénarios d'application. 1) MySQL fournit des fonctions efficaces de stockage de données, d'organisation et de récupération, en prenant en charge les systèmes Web, mobiles et de niveau d'entreprise. 2) Il utilise une architecture client-serveur, prend en charge plusieurs moteurs de stockage et optimisation d'index. 3) Les usages de base incluent la création de tables et l'insertion de données, et les usages avancés impliquent des jointures multiples et des requêtes complexes. 4) Des questions fréquemment posées telles que les erreurs de syntaxe SQL et les problèmes de performances peuvent être déboguées via la commande Explication et le journal de requête lente. 5) Les méthodes d'optimisation des performances comprennent l'utilisation rationnelle des indices, la requête optimisée et l'utilisation des caches. Les meilleures pratiques incluent l'utilisation des transactions et des acteurs préparés
Pourquoi utiliser MySQL? Avantages et avantages
Apr 12, 2025 am 12:17 AM
MySQL est choisi pour ses performances, sa fiabilité, sa facilité d'utilisation et son soutien communautaire. 1.MySQL fournit des fonctions de stockage et de récupération de données efficaces, prenant en charge plusieurs types de données et opérations de requête avancées. 2. Adoptez l'architecture client-serveur et plusieurs moteurs de stockage pour prendre en charge l'optimisation des transactions et des requêtes. 3. Facile à utiliser, prend en charge une variété de systèmes d'exploitation et de langages de programmation. 4. Avoir un solide soutien communautaire et fournir des ressources et des solutions riches.
Comment se connecter à la base de données d'Apache
Apr 13, 2025 pm 01:03 PM
Apache se connecte à une base de données nécessite les étapes suivantes: Installez le pilote de base de données. Configurez le fichier web.xml pour créer un pool de connexion. Créez une source de données JDBC et spécifiez les paramètres de connexion. Utilisez l'API JDBC pour accéder à la base de données à partir du code Java, y compris l'obtention de connexions, la création d'instructions, les paramètres de liaison, l'exécution de requêtes ou de mises à jour et de traitement des résultats.
Comment démarrer MySQL par Docker
Apr 15, 2025 pm 12:09 PM
Le processus de démarrage de MySQL dans Docker se compose des étapes suivantes: Tirez l'image MySQL pour créer et démarrer le conteneur, définir le mot de passe de l'utilisateur racine et mapper la connexion de vérification du port Créez la base de données et l'utilisateur accorde toutes les autorisations sur la base de données
Rôle de MySQL: Bases de données dans les applications Web
Apr 17, 2025 am 12:23 AM
Le rôle principal de MySQL dans les applications Web est de stocker et de gérer les données. 1.MySQL traite efficacement les informations utilisateur, les catalogues de produits, les enregistrements de transaction et autres données. 2. Grâce à SQL Query, les développeurs peuvent extraire des informations de la base de données pour générer du contenu dynamique. 3.MySQL fonctionne basé sur le modèle client-serveur pour assurer une vitesse de requête acceptable.
CentOS installe MySQL
Apr 14, 2025 pm 08:09 PM
L'installation de MySQL sur CENTOS implique les étapes suivantes: Ajout de la source MySQL YUM appropriée. Exécutez la commande YUM Install MySQL-Server pour installer le serveur MySQL. Utilisez la commande mysql_secure_installation pour créer des paramètres de sécurité, tels que la définition du mot de passe de l'utilisateur racine. Personnalisez le fichier de configuration MySQL selon les besoins. Écoutez les paramètres MySQL et optimisez les bases de données pour les performances.
