PlanetMysql.ru — информация о СУБД MySQL

Recently, I found myself needing MariaDB 5.1.60 for Windows for some testing purposes. Therefore, I needed to build it from source. I ended up using what I’d call a “blend” of the commands listed in this “how-to” and the readme file INSTALL-WIN-SOURCE, so I thought I’d post those steps.

1. Download 5.1.60 MariaDB source from here.

2. cd C:\mariadb-5.1

3. win\configure.js

4. cmake .

5. VS: File -> Open -> Solution -> MySql.sln
6. VS: Build -> Build Solution
7. VS: Right-click “PACKAGE” -> Build (in “Solution Explorer” View)

That’s it.

Let’s fire it up:

MariaDB> select version();
+----------------------+
| version()            |
+----------------------+
| 5.1.60-MariaDB-debug |
+----------------------+

MariaDB> show global variables like 'innodb_version';
+----------------+-------------+
| Variable_name  | Value       |
+----------------+-------------+
| innodb_version | 1.0.17-13.0 |
+----------------+-------------+

MariaDB> show engines;
+------------+---------+---------------------------...
| Engine     | Support | Comment		   ...
+------------+---------+---------------------------...
| CSV        | YES     | CSV storage engine	   ...
| InnoDB     | DEFAULT | Percona-XtraDB, Supports t...
| PBXT       | YES     | High performance, multi-ve...
| MARIA      | YES     | Crash-safe tables with MyI...
| MyISAM     | YES     | Default engine as of MySQL...
| FEDERATED  | YES     | FederatedX pluggable stora...
| MRG_MYISAM | YES     | Collection of identical My...
| MEMORY     | YES     | Hash based, stored in memo...
+------------+---------+---------------------------...

..

For reference, here are my full outputs:

C:\Users\Chris>cd ..\..\mariadb-5.1

C:\mariadb-5.1>win\configure.js

C:\mariadb-5.1>cmake .
-- Check for working C compiler: cl
-- Check for working C compiler: cl -- works
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Check for working CXX compiler: cl
-- Check for working CXX compiler: cl -- works
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
build CSV as static library (libcsv.lib)
build FEDERATEDX as static library (libfederatedx.lib)
build HEAP as static library (libheap_s.lib)
build MARIA as static library (libmaria_s.lib)
build MYISAM as static library (libmyisam_s.lib)
build MYISAMMRG as static library (libmyisammrg_s.lib)
build PBXT as static library (libpbxt_s.lib)
build XTRADB as static library (libxtradb.lib)
build ARCHIVE as DLL (ha_archive.dll)
build BLACKHOLE as DLL (ha_blackhole.dll)
build EXAMPLE as DLL (ha_example.dll)
build FEDERATED as DLL (ha_federated.dll)
build INNODB_PLUGIN as DLL (ha_innodb_plugin.dll)
-- Configuring done
-- Generating done
-- Build files have been written to: C:/mariadb-5.1

Open Visual Studio -> File -> Open -> Project/Solution -> Select C:\mariadb-5.1.60\MySql.sln

Build Solution Output:

========== Build: 79 succeeded, 0 failed, 2 up-to-date, 2 skipped ==========

Package Build Output:

1>------ Build started: Project: PACKAGE, Configuration: Debug Win32 ------
1>
1>Performing Post-Build Event...
1>CPack: Create package using NSIS
1>CPack: Install projects
1>CPack: - Install project: MySql
1>CPack: -   Install component: Unspecified
1>CPack: -   Install component: headers
1>CPack: -   Install component: mysqltest
1>CPack: -   Install component: runtime
1>CPack: -   Install component: scripts
1>CPack: -   Install component: sqlbench
1>CPack: Compress package
1>CPack: Finalize package
1>CPack: Package C:/mariadb-5.1/MariaDB-5.1.60-win32.exe generated.
1>Build log was saved at "file://c:\mariadb-5.1\PACKAGE.dir\Debug\BuildLog.htm"
1>PACKAGE - 0 error(s), 0 warning(s)
========== Build: 1 succeeded, 0 failed, 81 up-to-date, 0 skipped ==========

 
 

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL keeps many different files, some contain real data, some contain meta data. Witch ones are important? Witch can your throw away?

This is my attempt to create a quick reference of all the files used by MySQL, whats in them, what can you do if they are missing, what can you do with them.

When I was working for Dell doing Linux support my first words to a customer where “DO YOU HAVE COMPLETE AND VERIFIED BACKUP?” Make one now before you think about doing anything I suggest here.

You should always try to manage your data through a MySQL client.  If things have gone very bad this may not be possible. MySQL may not start. If your file system get corrupt you may have missing files. Sometimes people create other files in the MySQL directory (BAD).  This should help you understand what is safe to remove.

Before you try to work with one of these files make sure you have the file permissions set correctly.

This may not be a complete list of files used my MySQL.  It most certainly doesn’t describe everything each table is used for. If you know of ways to replace a missing file or what happens to MySQL when a file is missing that I haven’t described here, please leave me a comment or email me.  I’ll update this document and give your a reference.

my.cnf

This file alters the default configuration settings. MySQL looks in the /etc directory for my.cnf. You should review this file to insure you are looking in the right place for all other MySQL files.  MySQL WILL run without it.  If you have trouble getting MySQL to start, read the error log then try moving are renaming this file.

mysql <directory>

On Linux servers the default location for MySQL files is /var/lib/mysql.  This directory is controlled by the “datadir” variable.

Do I need to say, deleting this directory deletes everything?

ibdata1

If your remove this file your InnoDB DATA IS GONE and MySQL will recreate an empty file.

If you are not using the innodb_file_per_table option (default), this file holds almost ALL of your data in InnoDB tables.  This file is all but useless without its corresponding  ‘.frm’ file for each table in the right database directory. If all you have is the .frm files you can recreate the structure of your tables. (See below.)

Idbdata1 can get really big. The default size is 10MB. MySQL will automatically extended it by the default size as needed. If MySQL crashed, some of your InnoDB data may be in your transaction logs (ib_logfile.*).

By design the InnoDB file does not shrink. The safest way to shrink this file is to take a complete backup, stop MySQL, remove the ib* files, start MySQL and restore all your data.  REALLY.

DatabaseName <directory in mysql>

Each MySQL database has a directory named after it.  Each directory holds the meta data for the database. Your MyISAM data is in this directory. InnoDB tables may be here if the innodb_file_per_table variable is used. By default InnoDB tables are in the ibdata1 file (see above).

If you delete the directory your data may be gone. MyISAM data WILL be lost.  InnoDB tables may still be in the ibdata1.  If so, you will need to recreate the meta data files to recover your data.

Creating a directory is almost equivalent to ‘create database’.  If a directory exists MySQL will show you have a database by that name. The create database command may also creates a db.opt file.

<TableName>.frm

This file is key to both InnoDB and MyISAM databases. It is the meta data to the location of your data. It contains the table column definitions.

If you remove this file MySQL will tell you your DATA doesn’t exist.  It does. Your data is still in the ibdata (.ibd) file or the ibdata1 file. You need to recreate the table to recreate this file. If you don’t know the exact structure of this table your out of luck.

Stop MySQL and move the .MYD and .MYI files to another directory.  (You might also make a backup copy.) Start MySQL and recreate this table. Stop MySQL and copy the .MYD and .MYI files back to the database directory and restart MySQL.

<TableName>.MYD

THIS IS YOUR MyISAM DATA. If this is all you have, and you know the data structure of the the table, all is not lost.  (See .frm Above.)  You may also need to recreate the .MYI index file.

<TableName>.MYI

This file contains the indexs for your table.  If it becomes corrupt or is deleted you can recreate it using the ‘REPAIR TABLE table_name USE_FRM;’ command.

<TableName>.ibd

THIS IS YOUR InnoDB DATA. If this is all you have, and you know the data structure of the the table, all is not lost.  (See .frm Above.) Unlink MyISAM tables the indexes are contained in this file with your data.

MySQL doesn’t create this file unless you are using the innodb_file_per_table option.

<TableName>.CSV

THIS IS YOUR CSV DATA.  This file contains comma separated text data. These file do not have indexes.

<TableName>.CSM

This file contains meta data for CSV and archive tables.  I have not found what is stored here. I do know it tells MySQL if you are logging to the general logs.

ib_logfile*

This file contains your un-committed transactions data. MySQL uses it to recover from a crash.

If you shut down InnoDB cleanly, you can remove them. MySQL will recreate them.

If you change the size of innodb_log_file_size, you will need to recreate these files by stopping MySQL cleanly and deleting them.

mysql-bin.*

This “Bin Log” files contain any change made to any database. Each transaction is assigned a MASTER_LOG_POS(ion).  These files are not created by default. They are used for replication and point-in-time recovery.

You can stop the server and remove these files IF you remove the mysql-bin.index file as well.  MySQL creates a new bin log file each time it starts or the logs are flushed.  Deleting these files will the server is running will break replication.

mysql-bin.index

This fail is used by MySQL to keep a Bin Log list.  It is a simple text file like;

./mysql-bin.000001

./mysql-bin.000002

./mysql-bin.000003

If you remove this file, MySQL will recreate it with only the newest bin log name. If you need to remove old bin logs use the command “purge binary logs [to mysql-bin.######] [before “yyyy-mm-dd”]”.

You can control the number of bin logs using the expire_logs_days variable.

mysqld.log

This is MySQL’s primary administration log. MySQL reports starts and stops as well as some warning and errors in this file.  If MySQL crashes, mysqld_safe will restart it. This log will report this.

You can delete this file if needed.

slow.log

The slow query log consists of all SQL statements that took more than long_query_time seconds to execute and (as of MySQL 5.1.21) required at least min_examined_row_limit rows to be examined.

You can delete this file if needed.

db.opt

Database characteristics, like the CHARACTER SET clause are stored in the db.opt file. You may have strange query results if this file is missing. MySQL will use it’s default. You can recreate this file my altering the table with the correct settings.

mysql.pid

The PID file hold the process ID number for the running server. MySQL creates this file and scripts that start and stop MySQL use it to control MySQL.

MySQL will remove this file when is stops. You should not delete it if MySQL is running. If mysql is NOT running and the file exists MySQL may have crashed and you should delete this file.

References:

FULL DISK
http://dev.mysql.com/doc/refman/5.1/en/full-disk.html

MySQL Database Backup .MYI and .MYD
http://www.aeonity.com/frost/mysql-database-backup-myi-myd

Recovering from Crashes
http://dev.mysql.com/tech-resources/articles/recovering-from-crashes.html

Tweet

PlanetMySQL Voting: Vote UP / Vote DOWN

In database modeling, a m:n relationship is usually resolved by an additional table. But what if this relation is used only for archiving and the number of links in the resulting table is not too high? In that context, I got the idea to store all referring ID's as CSV string directly into a TEXT column of one of the referring tables. I came to this idea, because otherwise I would have to build complicated foreign keys and this way I also save one additional table. Certainly, this only makes sense if the data is not frequently accessed as foreign key. Nevertheless, I would like to tackle the problem, even if the implementation is very MySQL-oriented.

Read the rest »

PlanetMySQL Voting: Vote UP / Vote DOWN

Recently while we were building a slave with a newer version of MySQL 5.1 from an InnoDB Hot backup, the following error occurred when we ran mysql_upgrade:

mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
ERROR 13 (HY000) at line 311: Can't get stat of './mysql/general_log.CSV' (Errcode: 2)
ERROR 13 (HY000) at line 316: Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)
FATAL ERROR: Upgrade failed

The problem is that in MySQL 5.1, it is possible to log the slow query log and general log to tables in the mysql schema (source: Selecting General Query and Slow Query Log Output Destinations). These tables are created by default as CSV tables for performance reasons, and are created even if MySQL is set not to log to tables.

CSV tables, however, are not copied as part of the InnoDB Hot Backup process (by the wrapper script innobackupex.pl), thus creating this error. The fix to get the slave working was to copy the .CSV and .CSM files (each CSV table has three files: a .FRM file, a .CSV file, and a .CSM file), and re-run mysql_upgrade.

To avoid having to fix the CSV table issue every time, we looked at patching innobackup.pl to backup CSV tables properly. We found it involved a simple change. To fix it for InnoDB Hot Backup, simply replace line 1449 from the original:

    my $wildcard = '*.{frm,MYD,MYI,MRG,TRG,TRN,opt}';

to:

    my $wildcard = '*.{frm,MYD,MYI,MRG,TRG,TRN,CSM,CSV,opt}';

Xtrabackup, with its wrapper innobackupex.pl, also has the same issue with not backing up CSV tables. Similarly, the fix is to change line 1811 of innobackupex.pl from:

    my $wildcard = '*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}';

to:

    my $wildcard = '*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}';

We submitted this issue to Percona (innobackupex fails to backup CSV tables causing mysql_upgrade to fail) and to Innobase (innobackup.pl fails to backup CSV tables causing mysql_upgrade to fail).

PlanetMySQL Voting: Vote UP / Vote DOWN