==================================================================================== === Как сделать возможным автоматическое создание резервной копии базы данных, сохранив логин\пароль в файле .my.cnf: ==================================================================================== # vi .my.cnf [mysqldump] password=my_password ==================================================================================== === Указать путь к .my.cnf: ==================================================================================== # mysqldump --defaults-file=/path-to-file/.my.cnf –u my_username my_db > my_db.sql ==================================================================================== === Задать новый пароль для mysql-пользователя (разные варианты): ==================================================================================== # SET PASSWORD FOR 'root'@'localhost' = PASSWORD("password"); == Или == # USE dbname; UPDATE user SET password=PASSWORD("password") WHERE User='user' AND Host="localhost"; == Или == # UPDATE mail.virtual_users SET password=ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))) WHERE email='test@1.aa'; == Или == # mysqladmin -u root -p'oldpassword' password newpass == Или == # ALTER USER 'user'@'localhost' IDENTIFIED BY 'password'; ==================================================================================== === Создать дамп базы: ==================================================================================== # mysqldump -u user_name -p db_name | gzip > /path/to/sql.gz ==================================================================================== === Восстановить базу из дампа: ==================================================================================== # mysql -u username -p dbname < /path/to/dump.sql ==================================================================================== === Сделать бэкап конкретной таблицы: ==================================================================================== # mysqldump -u username -p dbname.tablename | gzip > backup.sql.gz ==================================================================================== === Восстановить таблицу из бэкапа: ==================================================================================== # mysql -u username -p dbname < /path/to/table_name.sql ==================================================================================== === Восстановить базу из sql.gz: ==================================================================================== # zcat db.sql.gz | mysql -u username -p dbname ==================================================================================== === Отследить медленные запросы: ==================================================================================== # vi /etc/mysql/my.cnf slow-query-log = 1 slow-query-log-file = /var/log/mysql/slow.log long_query_time = 1 log-queries-not-using-indexes == В файл /var/log/mysql/slow.log будут попадать запросы, которые выполняются медленнее 1 секунды, а также запросы, которые не используют индексы. Кроме самих запросов там будет много сопутствующей информации. == ==================================================================================== === Изменить данные ячейки таблицы: ==================================================================================== # UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] [WHERE where_condition] == Пример: == # UPDATE config_common SET valueparam=0 WHERE id=74 ==================================================================================== === Просмотреть размер таблиц в базе данных: ==================================================================================== # SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = "DBNAME" ORDER BY (data_length + index_length) DESC; ==================================================================================== === Открыть базу на другой системе: ==================================================================================== # tar -cvzf mysql.tar.gz var/lib/mysql vi /etc/mysql/my.cnf datadir = /path/to/unzipped/tar/gz/ == Если пароль пользователя mysql известен, то можно подключаться к перенесенной базе (единственный раз, когда я прибегал к этой инструкции, всё было именно так). Если нет, то (но зачем, я так и не понял): == # grep debian-sys-maint_password /etc/mysql/debian.cnf /usr/bin/mysqld_safe --skip-grant-tables & USE mysql UPDATE user SET PASSWORD=PASSWORD("the password from /etc/mysql/debian.cnf") WHERE User='debian-sys-maint'; UPDATE user SET PASSWORD=PASSWORD("your root password") WHERE User='root'; /etc/init.d/mysql restart ==================================================================================== === Найти колонку с заданным названием в любой из баз, доступных пользователю: ==================================================================================== # USE information_schema; SELECT * FROM columns WHERE column_name = 'column_name'; ==================================================================================== === Сбросить пароль root: ==================================================================================== # /etc/init.d/mysql stop mysqld_safe --skip-grant-tables & mysql -u root USE mysql; update user set authentication_string=password('mynewpassword') where user='root'; FLUSH PRIVILEGES; EXIT; /etc/init.d/mysql stop /etc/init.d/mysql start ==================================================================================== === Удалить колонку: ==================================================================================== # ALTER TABLE table_name DROP COLUMN column_name; ==================================================================================== === Как найти foreign key для любой связи: ==================================================================================== # USE information_schema; SELECT * FROM KEY_COLUMN_USAGE; ==================================================================================== === Удалить foreign key: ==================================================================================== # ALTER TABLE table_name DROP FOREIGN KEY key_id; ==================================================================================== === Удалить строку: ==================================================================================== # DELETE FROM table_name where id=1 limit 1; ==================================================================================== === Как дать удаленному пользователю доступ к базе (с учетом, что в my.cnf уже внесены изменения): ==================================================================================== # CREATE USER 'user_remote'@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON db_name.* TO 'user_remote'@'%'; FLUSH PRIVILEGES; ==================================================================================== === Переименовать колонку: ==================================================================================== # ALTER TABLE `table_name` CHANGE COLUMN `old_column_name` `new_column_name` VARCHAR(255) NOT NULL; ==================================================================================== === Установить libmysqlclient-dev в Debian 9: ==================================================================================== # wget mysql-apt-config_X.X.X_all.deb (https://repo.mysql.com) dpkg -i mysql-apt-config_X.X.X_all.deb apt-get update apt-get install mysql-server mysql-client libmysqlclient-dev ==================================================================================== === Задать пароль для root MariaDB: ==================================================================================== # SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password'); UPDATE mysql.user SET plugin = '' WHERE user = 'root' AND host = 'localhost'; FLUSH PRIVILEGES; ==================================================================================== === SQL Assoc Failed!, Error: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'concat' === ==================================================================================== # SET NAMES 'charset_name' [COLLATE 'collation_name'] SET NAMES 'character_set_database' [COLLATE 'utf8_general_ci'] ==================================================================================== === Настроить доступ к MySQL по паролю для root (альтернативный метод аутентификации стал использоваться, вроде бы, с 5.8): ==================================================================================== # ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'nsdljkYI&*t3'; ==================================================================================== === Изменить пароль пользователя MySQL: ==================================================================================== # UPDATE some_db.db_users SET password=ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))) WHERE email='user@example.com'; ==================================================================================== === Создать пользователя в базе Azure SQL с помощью SMSS: ==================================================================================== # == in master db: == CREATE LOGIN username WITH PASSWORD=N'password' == in db: == CREATE USER username FROM LOGIN username; EXEC sp_addrolemember N'db_owner', N'username' GO ==================================================================================== === Docker MySql содержит пользователей 'root'@'localhost' and 'root'@'%'. Не путать! ==================================================================================== ==================================================================================== === Изменить пароль залогиненого пользователя: ==================================================================================== # SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass'); ==================================================================================== === Получить character_set и collation текущей базы данных: ==================================================================================== # SELECT @@character_set_database, @@collation_database; ==================================================================================== === Изменить character_set и collation базы данных: ==================================================================================== # ALTER DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; ==================================================================================== === Получить collation таблиц базы данных: ==================================================================================== # SELECT table_schema, table_name, table_collation FROM information_schema.tables WHERE table_schema = 'db_name'; ==================================================================================== === Изменить character_set (нужно ли?) и collation всех таблиц базы данных: ==================================================================================== # SELECT CONCAT('ALTER TABLE ', tbl.TABLE_SCHEMA, '.', tbl.TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') FROM information_schema.TABLES tbl WHERE tbl.TABLE_SCHEMA = 'db_name'; ==================================================================================== === Изменить character_set (нужно ли?) и collation конкретной таблицы текущей базы данных: ==================================================================================== # ALTER TABLE table_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; ==================================================================================== === Сделать возможным изменение character_set и collation в колонках таблицы: ==================================================================================== # SET foreign_key_checks = 0; ==================================================================================== === Получить character_set и collation всех колонок всех таблиц базы данных: ==================================================================================== # SELECT table_name, character_set_name FROM information_schema.columns WHERE table_schema = 'db_name'; ==================================================================================== === Получить список всех колонок таблиц базы данных, которые не совпадают с указанной collation_name: ==================================================================================== # SELECT * FROM information_schema.COLUMNS WHERE table_schema = 'db_name' AND collation_name != 'utf8mb4_general_ci'; ==================================================================================== === Изменить character_set и collation всех колонок конкретной таблицы: ==================================================================================== # ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; ==================================================================================== === Запретить дальнейшие изменения в таблицах: ==================================================================================== # SET foreign_key_checks = 1; ==================================================================================== === Экспортировать таблицу в csv и импортировать обратно в таблицу. Не забывать восстанавливать Foreign Key, потому что "RESEED, 0" ломает связи между таблицами, но без этого корректно импортировать таблицу не удавалось: ==================================================================================== # == EXPORT: == bcp $TABLE_NAME out C:\Users\user\file.txt -S "$SQL_SERVER_NAME.database.windows.net" -d "$DATABASE_NAME" -U "$USER_NAME" -P $PASSWORD -c == TRUNCATE: == Invoke-Sqlcmd -Query "DELETE FROM $TABLE_NAME; DBCC CHECKIDENT ('$SQL_SERVER_NAME.$TABLE_NAME', RESEED, 0);" -ServerInstance "$SQL_SERVER_NAME.database.windows.net" -Database "$DATABASE_NAME" -Username "$USER_NAME" -Password $PASSWORD == IMPORT: == bcp $TABLE_NAME in C:\Users\user\file.txt -S "$SQL_SERVER_NAME.database.windows.net" -d "$DATABASE_NAME" -U "$USER_NAME" -P $PASSWORD -c ==================================================================================== === Вывести размер таблиц базы: ==================================================================================== # SELECT CONCAT(table_schema, '.', table_name), CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows, CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA, CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size, ROUND(index_length / data_length, 2) idxfrac FROM information_schema.TABLES ORDER BY data_length + index_length DESC; ==================================================================================== === Подключиться к удаленной базе: ==================================================================================== # mysql -u $USER -h $HOST -P $PORT -p $DB ==================================================================================== === Импортировать bacpac, созданный средствами mssms из Azure SQL, в базу AWS RDS, которая будет создаваться во время импорта: ==================================================================================== # ./SqlPackage.exe /a:Import /sf:"C:\dbbackup.bacpac" /tsn:"$rdsservername.rds.amazonaws.com" /tdn:"$new_db_name" /tu:"$dbroot" /tp:"$dbroot_password" /TargetTrustServerCertificate:true ==================================================================================== === Создать бэкап базы postgres: ==================================================================================== # pg_dump --host example.com --port 5432 --user user dbname > backup.sql ==================================================================================== === Войти в базу postgres: ==================================================================================== # psql -h example.com -p 5432 -d dbname -U dbuser ==================================================================================== === Импорт бэкапа базы postgres: ==================================================================================== # psql -f "C:\Users\user\backup.sql" dbname -h example.com -U dbuser ==================================================================================== === Создать пользователя postgres: ==================================================================================== # CREATE ROLE dbuser WITH LOGIN PASSWORD 'password';