У даній шпаргалці по 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' ;