Это старая версия документа!

, , , ,

MySql

Установка

MariaDB: 

apt-get install libmariadbclient18 libmysqlclient18 mariadb-client mariadb-client-10.0 mariadb-client-core-10.0 mariadb-common  mariadb-server

Инициализация

dpkg-reconfigure mysql-server-5.1 - установка root пароля
mysql_upgrade -u root -p --force - ремонт баз данных
mysqlcheck -uroot -p --auto-repair --check --optimize --all-databases - ремонт баз данных

mysql_install_db - создание mysql database
mysql_install_db --rpm --user=mysql
mysql_install_db --defaults-file=/etc/mysql/my.cnf
mysql_install_db --no-defaults --basedir=/usr --datadir=/var/lib/mysql
mysql_install_db --defaults-file=/etc/mysql/my.cnf --force --basedir=/usr --datadir=/var/lib/mysql --user=mysql

Настройка и оптимизация

Dump

mysqldump -u[uname] -p[pass] > mysql.sql
mysqldump -u[uname] -p[pass] [dbname] | gzip -9 > backupfile.sql.gz
mysqldump --hex-blob -A > all_databases.sql
mysqldump --all-databases -u[uname] -p[pass] > dump.sql

mysqldump --host=localhost --user=root --password=password --opt --skip-dump-date --skip-extended-insert --database [table-name] > /tmp/mydatabase.sql

Утилиты

Restore

mysql -u[uname] -p[pass] database_name < file_name.sql
gunzip -c /path/backup.Sql.Gz | mysql -h -u[uname] -p[pass]
gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

/usr/bin/gunzip < database.gz database.sql
mysql -u[uname] -p[pass] username_dbname < database.sql

mysql -u[user] -p database < database.sql
mysql>source /path/to/database.sql

Утилиты

Ошибки

  • Got error 28 from storage engine - Закончилось место на диске
  • MySQL Error: Out of resources when opening file './db_name/table_name.MYD' (Errcode: 24)

SQL

SHOW DATABASES;
SHOW TABLES;
CREATE DATABASE rxmeditrend;
USE rxmeditrend;
 
SELECT USER, Host FROM mysql.user; # List ALL the users IN the system
CREATE USER 'username' IDENTIFIED BY 'password';
GRANT ALL ON rxmeditrend.* TO 'username';
SHOW GRANTS FOR 'username'@'host';
SET PASSWORD FOR 'username'@'host' = PASSWORD('password');
FLUSH PRIVILEGES; # FLUSH privileges after manually manipulating the USER TABLE
 
SHOW TABLE STATUS;

MyISAM

  • tbl_name.frm: Table definition
  • tbl_name.MYD: Table data
  • tbl_name.MYI: Table indexes 
CHECK TABLE tbl_name QUICK;
REPAIR TABLE tbl_name EXTENDED;

InnoDB

Восстановление

  • ibdata1: By default all data and indexes for all tables/databases is stored in a single, shared file: ibdata1. idbata1 does not shrink. If rows are deleted, space will be reclaimed by new rows, but the file will only grow in size.
  • ib_logfile0 and ib_logfile1: Redo log files to recover lost data if MySQL is not shutdown properly.
  • tablename.frm: Each table has a small *.frm file in the database-specific directory which stores the table definition.

Ссылки

Движки

Работа MySQL

Миграции

Обслуживание MySql

Индексы

Деревья

Книги

Безопасность

Общее