У даній шпаргалці по MySQL ми розповімо про скидання пароля mysql, скріпт для додавання користувача, як показати всіх користувачів і інше.

Коннект до mysql по TCP

mysql --user root --host localhost --port 3306 --protocol tcp

Створити БД

CREATE DATABASE $DB_NAME character set utf8;

Додати користувача

CREATE USER $DB_USER@localhost IDENTIFIED BY '$DB_PASSWORD';

Дати права користувачеві на БД

GRANT ALL ON $DB_NAME.* TO $DB_USER@localhost;

• на віддалений доступ:

GRANT ALL PRIVILEGES ON $DB_NAME.* TO $DB_USER@'%' IDENTIFIED BY '$DB_PASSWORD'

• на віддалений доступ на всі таблиці:

grant all privileges on *.* to DB_USER@'%';

Змінити пароль користувачу

SET PASSWORD FOR логин@localhost = PASSWORD('пароль'); SET PASSWORD FOR логин@"%" = PASSWORD('пароль');

• що б права вступили в силу:

flush privileges;

Подивитися права користувача

SHOW GRANTS for $DB_USER@localhost;

Якщо потрібно залити великий дамп, то в /etc/my.cnf в секцію [mysqld] додати:

max_allowed_packet = 16M

Робота з кластером майстер-слейв

• подивитися стан майстра:

show master status;

• подивитися стан слейва:

SHOW SLAVE STATUS\G

Подивитися структури таблиці в БД

describe <table_name>;

або

SHOW CREATE TABLE <table_name>;

Подивитися стан таблиць в БД

SHOW TABLE STATUS;

Є ще краще спосіб:

DELIMITER $$
 DROP PROCEDURE IF EXISTS `tools`.`sp_status` $$
 CREATE PROCEDURE `tools`.`sp_status`(dbname VARCHAR(50))
 BEGIN 
 -- Obtaining tables and views
 (
     SELECT 
      TABLE_NAME AS `Table Name`, 
      ENGINE AS `Engine`,
      TABLE_ROWS AS `Rows`,
      CONCAT(
         (FORMAT((DATA_LENGTH + INDEX_LENGTH) / POWER(1024,2),2))
         , ' Mb'
        AS `Size`,
      TABLE_COLLATION AS `Collation`
     FROM information_schema.TABLES
     WHERE TABLES.TABLE_SCHEMA = dbname
           AND TABLES.TABLE_TYPE = 'BASE TABLE'
 )
 UNION
 (
     SELECT 
      TABLE_NAME AS `Table Name`, 
      '[VIEW]' AS `Engine`,
      '-' AS `Rows`,
      '-' `Size`,
      '-' AS `Collation`
     FROM information_schema.TABLES
     WHERE TABLES.TABLE_SCHEMA = dbname 
           AND TABLES.TABLE_TYPE = 'VIEW'
 )
 ORDER BY 1;
 -- Obtaining functions, procedures and triggers
 (
     SELECT ROUTINE_NAME AS `Routine Name`, 
      ROUTINE_TYPE AS `Type`,
      '' AS `Comment`
     FROM information_schema.ROUTINES
     WHERE ROUTINE_SCHEMA = dbname
     ORDER BY ROUTINES.ROUTINE_TYPE, ROUTINES.ROUTINE_NAME
 )
 UNION
 (
     SELECT TRIGGER_NAME,'TRIGGER' AS `Type`, 
     concat('On ',EVENT_MANIPULATION,': ',EVENT_OBJECT_TABLE) AS `Comment`
     FROM information_schema.TRIGGERS
     WHERE EVENT_OBJECT_SCHEMA = dbname
 )
 ORDER BY 2,1;
 END$$
 DELIMITER ;

Скидання пароля mysql

1) У  /etc/mycnf в секцію [mysqld] вставляємо рядок  skip-grant-tables

2) Перезапустити mysqld.

Після цього пароль буде скинутий і можна ввести будь-який інший.

Cкріпт для додавання користувача

mysql-add_user:

#!/bin/bash

if [ $# -lt 3 ]; then
    echo "Usage: mysql-add_user <user> <password> <database>"
    exit
fi

USER=$1
PASS=$2
DB=$3

echo "CREATE USER $USER@localhost IDENTIFIED BY '$PASS';"
echo "CREATE USER $USER@localhost IDENTIFIED BY '$PASS';" | mysql

echo "GRANT ALL ON $DB.* TO $USER@localhost"
echo "GRANT ALL ON $DB.* TO $USER@localhost" | mysql

echo "GRANT ALL ON  $DB.* TO $USER@'%' IDENTIFIED BY '$PASS';"
echo "GRANT ALL ON  $DB.* TO $USER@'%' IDENTIFIED BY '$PASS';" | mysql

echo "flush privileges;"
echo "flush privileges;" | mysql

echo "Done!"

Додати індекс в таблицю wp_options по полю autoload

ALTER TABLE wp_options ADD INDEX (`autoload`);

Показати індекси для таблиці

SHOW INDEXES FROM wp_options;

Дізнатися як виконується запит і які індекси використовує

EXPLAIN SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

Показати всіх користувачів

SELECT User,Host FROM mysql.user;

Те ж саме, але відразу в форматі, який можна використовувати в DROP user:

SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) UserAccount FROM mysql.user;

Видалити користувача

Станом на MySQL 5.0.2 ви можете видалити обліковий запис наступним чином: DROP USER user; 

Вивести результат в файл:

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/passwd.txt'
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n';

Запис логу всіх запитів до БД

вивід лога в таблицю (в БД mysql):

CREATE TABLE `general_log` (
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
                      ON UPDATE CURRENT_TIMESTAMP,
`user_host` mediumtext NOT NULL,
`thread_id` bigint(21) unsigned NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`command_type` varchar(64) NOT NULL,
`argument` mediumtext NOT NULL
  ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';

Увімкнути журнал запитів у базі даних

SET global log_output = 'таблиця' ;
SET global general_log = 1 ;

Переглянути журнал

select * from mysql.general_log

Вимкнути журнал запитів у базі даних

SET global general_log = 0 ;

Вивід лога в файл:

SET global log_output = 'FILE';
SET global general_log_file='/Applications/MAMP/logs/mysql_general.log';
SET global general_log = 1;

Визначити розмір бази MYSQL

команда:

SELECT SUM( data_length + index_length ) AS 'size'
FROM information_schema.TABLES
WHERE table_schema = 'mydatabase'
LIMIT 1;

Або скрипт mysql-db_size:

#!/bin/bash

if [ $# -lt 1 ]; then
    echo "Usage: mysql-db_lenth <database>"
    exit
fi

DB=$1

LENTH=`echo "SELECT SUM( data_length + index_length ) AS 'size' FROM information_schema.TABLES WHERE table_schema = '$DB' LIMIT 1;" | mysql | sed -n '2p;2q'`

let LENTH_MB=$LENTH/1024/1024
let LENTH_GB=$LENTH_MB/1024

echo "$LENTH_GB Gb"
echo "$LENTH_MB Mb"
echo "$LENTH b"

Показати розмір таблиць в БД

SELECT TABLE_NAME, (data_length+index_length)/1024/1024/1024 as "size, Gb" FROM information_schema.TABLES WHERE table_schema = '<database>' order by "size, Gb";

скрипт table_sizes.sh

#!/bin/bash

if [ $# -lt 1 ]; then
    echo "Usage: ./table_sizes.sh <database>"
    exit
fi
DB=$1

echo "SELECT TABLE_NAME, (data_length+index_length)/1024/1024/1024 as 'size, Gb' FROM information_schema.TABLES WHERE table_schema = '$DB'" | mysql

Скрипт резервного копіювання всіх баз на сервері

#!/bin/bash

TIMESTAMP=`date +"%Y%m%d"`
BACKUPSTORE=/root/backup/dumps
FILENAME="mysql-full-dump.${TIMESTAMP}.bz2"
BACKUP_OWNER=root
RETENTION=90 # days

/usr/bin/mysqldump --all-databases --flush-logs --force --add-drop-database | bzip2 -s > $BACKUPSTORE/$FILENAME
chown $BACKUP_OWNER $BACKUPSTORE/$FILENAME
find $BACKUPSTORE -ctime +$RETENTION -delete


Прибрати заголовки у виводі mysql

mysql --skip-column-names

Вивід всіх запитів в лог

SET GLOBAL general_log_file = "/tmp/sql.log" ;
SET GLOBAL general_log = 'ON' ;
SET GLOBAL general_log = 'OFF' ;