mysql check database & repair table

Pour réparer une table signalée en erreur  comme :

mysqld: 151224 12:10:27 [ERROR] /usr/sbin/mysqld: Table './metar_2013/M05_error' is marked as crashed and should be repaired

Utiliser la commande mysql

repair table nombase.nomtable;

mysqlcheck — A Database Maintenance Program

shell> mysqlcheck [options] db_name [tbl_name ...]
shell> mysqlcheck [options] --databases db_name ...
shell> mysqlcheck [options] --all-databases

The names shown in the following table can be used to change mysqlcheck default behavior.

Command	Meaning
mysqlrepair	The default option is --repair
mysqlanalyze	The default option is --analyze
mysqloptimize	The default option is --optimize

mysqlrepair:   The default option will be -r
mysqlanalyze:  The default option will be -a
mysqloptimize: The default option will be -o

Usage: mysqlcheck [OPTIONS] database [tables]
OR     mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...]
OR     mysqlcheck [OPTIONS] --all-databases

Default options are read from the following files in the given order:
/etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
The following groups are read: mysqlcheck client
The following options may be given as the first argument:
--print-defaults    Print the program argument list and exit
--no-defaults        Don't read default options from any options file
--defaults-file=#    Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read
  -A, --all-databases Check all the databases. This will be same as
                      --databases with all databases selected.
  -a, --analyze       Analyze given tables.
  -1, --all-in-1      Instead of issuing one query for each table, use one
                      query per database, naming all tables in the database in
                      a comma-separated list.
  --auto-repair       If a checked table is corrupted, automatically fix it.
                      Repairing will be done after all tables have been
                      checked, if corrupted ones were found.
  --character-sets-dir=name
                      Directory where character sets are.
  -c, --check         Check table for errors.
  -C, --check-only-changed
                      Check only tables that have changed since last check or
                      haven't been closed properly.
  -g, --check-upgrade Check tables for version-dependent changes. May be used
                      with --auto-repair to correct tables requiring
                      version-dependent updates.
  --compress          Use compression in server/client protocol.
  -B, --databases     To check several databases. Note the difference in usage;
                      In this case no tables are given. All name arguments are
                      regarded as databasenames.
  -#, --debug[=#]     This is a non-debug version. Catch this and exit.
  --default-character-set=name
                      Set the default character set.
  -F, --fast          Check only tables that haven't been closed properly.
  -f, --force         Continue even if we get an sql-error.
  -e, --extended      If you are using this option with CHECK TABLE, it will
                      ensure that the table is 100 percent consistent, but will
                      take a long time. If you are using this option with
                      REPAIR TABLE, it will force using old slow repair with
                      keycache method, instead of much faster repair by
                      sorting.
  -?, --help          Display this help message and exit.
  -h, --host=name     Connect to host.
  -m, --medium-check  Faster than extended-check, but only finds 99.99 percent
                      of all errors. Should be good enough for most cases.
  -o, --optimize      Optimize table.
  -p, --password[=name]
                      Password to use when connecting to server. If password is
                      not given it's solicited on the tty.
  -P, --port=#        Port number to use for connection or 0 for default to, in
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
                      /etc/services, built-in default (3306).
  --protocol=name     The protocol of connection (tcp,socket,pipe,memory).
  -q, --quick         If you are using this option with CHECK TABLE, it
                      prevents the check from scanning the rows to check for
                      wrong links. This is the fastest check. If you are using
                      this option with REPAIR TABLE, it will try to repair only
                      the index tree. This is the fastest repair method for a
                      table.
  -r, --repair        Can fix almost anything except unique keys that aren't
                      unique.
  -s, --silent        Print only error messages.
  -S, --socket=name   Socket file to use for connection.
  --ssl               Enable SSL for connection (automatically enabled with
                      other flags). Disable with --skip-ssl.
  --ssl-ca=name       CA file in PEM format (check OpenSSL docs, implies
                      --ssl).
  --ssl-capath=name   CA directory (check OpenSSL docs, implies --ssl).
  --ssl-cert=name     X509 cert in PEM format (implies --ssl).
  --ssl-cipher=name   SSL cipher to use (implies --ssl).
  --ssl-key=name      X509 key in PEM format (implies --ssl).
  --ssl-verify-server-cert
                      Verify server's "Common Name" in its cert against
                      hostname used when connecting. This option is disabled by
                      default.
  --tables            Overrides option --databases (-B).
  --use-frm           When used with REPAIR, get table structure from .frm
                      file, so the table can be repaired even if .MYI header is
                      corrupted.
  -u, --user=name     User for login if not current user.
  -v, --verbose       Print info about the various stages.
  -V, --version       Output version information and exit.
 

Add & Remove system V init links update-rc.d

update-rc.d - Pour installer ou supprimer les liens vers les scripts d'initialisation de type System-V

Par exemple pour mysql :

update-rc.d mysql defaults 21

update-rc.d mysql-ndb defaults 22

update-rc.d mysql-ndb-mgm defaults 23

Voir aussi install sysv-rc.conf

how to know what is the active database in mysql

select database();

GRANT ALL PRIVILEGES : MYSQL

Pour donner accès aux dB à un utilisateur :

Se logger comme root dans mysql

mysql -u root mysql -p

au prompt : password

puis

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY '' WITH GRANT OPTION;

flush privileges;

Pour vérifier :

SELECT user, host, password FROM user;

à la place de localhost = connection uniquement en local, on peut mettre % pour pouvoir se logger à distance.

mysql backup

Les fichiers des db se trouvent (ubuntu 8.04)

/var/lib/mysql/

On peut simplement faire une copie des fichiers mais il faut mettre un lock write avant.

C'est ce que fait le script mysqlhotcopy

Syntaxe :

A exécuter comme root

mysqlhotcopy metar_2009 -u meteo -p '' --addtodest /tmp/mysqlbackup

mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory

--addtodest

Do not rename target directory (if it exists); merely add files to it.

--dryrun, -n

Report actions without performing them.

--method=command

The method for copying files (cp or scp).

--password=password, -ppassword

The password to use when connecting to the server. Note that the password value is not optional for this option, unlike for other MySQL programs. You can use an option file to avoid giving the password on the command line.

--user=user_name, -u user_name

The MySQL user name to use when connecting to the server.

6.1. Database Backups

This section summarizes some general methods for making backups.

Making Backups by Copying Files

MyISAM tables are stored as files, so it is easy to do a backup by copying files. To get a consistent backup, do aLOCK TABLES on the relevant tables, followed by FLUSH TABLES for the tables. See Section 12.4.5, “LOCK TABLES and UNLOCK TABLES Syntax”, and Section 12.5.6.3, “FLUSH Syntax”. You need only a read lock; this allows other clients to continue to query the tables while you are making a copy of the files in the database directory. The FLUSH TABLES statement is needed to ensure that the all active index pages are written to disk before you start the backup.

Making Delimited-Text File Backups

To create a text file containing a table's data, you can use SELECT * INTO OUTFILE 'file_name' FROMtbl_name. The file is created on the MySQL server host, not the client host. For this statement, the output file cannot already exist because allowing files to be overwritten would constitute a security risk. See Section 12.2.8, “SELECT Syntax”. This method works for any kind of data file, but saves only table data, not the table structure.

To reload the output file, use LOAD DATA INFILE or mysqlimport.

Making Backups with mysqldump or mysqlhotcopy

Another technique for backing up a database is to use the mysqldump program or the mysqlhotcopy script.mysqldump is more general because it can back up all kinds of tables. mysqlhotcopy works only with some storage engines. (See Section 4.5.4, “mysqldump — A Database Backup Program”, and Section 4.6.9, “mysqlhotcopy — A Database Backup Program”.)

Create a full backup of your database:

shell> mysqldump --tab=/path/to/some/dir --opt db_name

Or:

shell> mysqlhotcopy db_name /path/to/some/dir

You can also create a binary backup simply by copying all table files (*.frm, *.MYD, and *.MYI files), as long as the server isn't updating anything. The mysqlhotcopy script uses this method. (But note that these methods do not work if your database contains InnoDB tables. InnoDB does not necessarily store table contents in database directories, and mysqlhotcopy works only for MyISAM and ISAM tables.)

For InnoDB tables, it is possible to perform an online backup that takes no locks on tables; see Section 4.5.4, “mysqldump — A Database Backup Program”.

MYSQL FROM_UNIXTIME, DATE_FORMAT, SQLITE3,

Avec sqlite3 j'utilise strftime('%w',unixtime,'unixepoch','localtime') pour obtenir le jour de la semaine sous forme numérique de 0 à 6

strftime('%w',unixtime,'unixepoch','localtime')

Avec mysql la commande équivalente est :

FROM_UNIXTIME(unixtime,'%w')

Pas besoin de préciser localtime, car la conversion se fait directement selon le timezone (par défaut SYSTEM)

FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)

Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' orYYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone. unix_timestamp is an internal timestamp value such as is produced by the UNIX_TIMESTAMP() function.

If format is given, the result is formatted according to the format string, which is used the same way as listed in the entry for the DATE_FORMAT() function.

mysql> SELECT FROM_UNIXTIME(1196440219);
-> '2007-11-30 10:30:19'
mysql> SELECT FROM_UNIXTIME(1196440219) + 0;
-> 20071130103019.000000
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
->                      '%Y %D %M %h:%i:%s %x');
-> '2007 30th November 10:30:59 2007'

Note: If you use UNIX_TIMESTAMP() and FROM_UNIXTIME() to convert between TIMESTAMP values and Unix timestamp values, the conversion is lossy because the mapping is not one-to-one in both directions. For details, see the description of the UNIX_TIMESTAMP() function.

• DATE_FORMAT(date,format)

  Formats the date value according to the format string.

  The following specifiers may be used in the format string. The “%” character is required before format specifier characters.

  Specifier	Description
  %a	Abbreviated weekday name (Sun..Sat)
  %b	Abbreviated month name (Jan..Dec)
  %c	Month, numeric (0..12)
  %D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
  %d	Day of the month, numeric (00..31)
  %e	Day of the month, numeric (0..31)
  %f	Microseconds (000000..999999)
  %H	Hour (00..23)
  %h	Hour (01..12)
  %I	Hour (01..12)
  %i	Minutes, numeric (00..59)
  %j	Day of year (001..366)
  %k	Hour (0..23)
  %l	Hour (1..12)
  %M	Month name (January..December)
  %m	Month, numeric (00..12)
  %p	AM or PM
  %r	Time, 12-hour (hh:mm:ss followed by AM or PM)
  %S	Seconds (00..59)
  %s	Seconds (00..59)
  %T	Time, 24-hour (hh:mm:ss)
  %U	Week (00..53), where Sunday is the first day of the week
  %u	Week (00..53), where Monday is the first day of the week
  %V	Week (01..53), where Sunday is the first day of the week; used with %X
  %v	Week (01..53), where Monday is the first day of the week; used with %x
  %W	Weekday name (Sunday..Saturday)
  %w	Day of the week (0=Sunday..6=Saturday)
  %X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
  %x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
  %Y	Year, numeric, four digits
  %y	Year, numeric (two digits)
  %%	A literal “%” character
  %x	x, for any “x” not listed above

  Ranges for the month and day specifiers begin with zero due to the fact that MySQL allows the storing of incomplete dates such as '2014-00-00'.

  The language used for day and month names and abbreviations is controlled by the value of the lc_time_namessystem variable (Section 9.8, “MySQL Server Locale Support”).

  DATE_FORMAT() returns a string with a character set and collation given by character_set_connection andcollation_connection so that it can return month and weekday names containing non-ASCII characters.

  mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
  -> 'Sunday October 2009'
  mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
  -> '22:23:00'
  mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
  ->                 '%D %y %a %d %m %b %j');
  -> '4th 00 Thu 04 10 Oct 277'
  mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
  ->                 '%H %k %I %r %T %S %w');
  -> '22 22 10 10:23:00 PM 22:23:00 00 6'
  mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
  -> '1998 52'
  mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
  -> '00'