mysql

операции в mysql

чтобы скопировать всю таблицу

CREATE TABLE `table_dest` AS SELECT * FROM `table_src` WHERE 1

чтобы скопировать только структуру таблицы

CREATE TABLE `table_dest` AS SELECT * FROM `table_src` WHERE 0

создать таблицу

CREATE TABLE `users` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`login` CHAR( 20 ) NOT NULL ,
`pass` CHAR( 20 ) NOT NULL,
`group` CHAR( 20 ) NOT NULL
);

добавить столбец в таблицу

ALTER TABLE `users` ADD `group` CHAR(30) [FIRST | AFTER column_name ];

удалить столбец из таблицы
 

LOAD DATA INFILE ERROR 1045 (28000): Access denied for user

GRANT FILE ON *.* TO USER@localhost

Verify MySQL replication integrity

Запустить тест:
pt-table-checksum --no-check-replication-filters

Проверить результаты:
pt-table-checksum --no-check-replication-filters --replicate-check-only

MySQL Character Set Support

Server Character Set and Collation

character_set_server collation_server

The server character set and collation are used as default values if the database character set and collation are not specified in CREATE DATABASE statements. They have no other purpose.

Database Character Set and Collation

character_set_database and collation_database

MySQL Performance Tuning: EXPLAIN

EXPLAIN Types

system              The table has only one row 
const               At the most one matching row, treated as a constant 
eq_ref              One row per row from previous tables 
ref                 Several rows with matching index value 
ref_or_null         Like ref, plus NULL values 
index_merge         Several index searches are merged 
unique_subquery     Same as ref for some subqueries 
index_subquery      As above for non-unique indexes 
range               A range index scan 
index               The whole index is scanned 

Advanced MySQL Replication Techniques

http://onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.htm...

In this broader schema, water is the master of air and slave of earth, which is slave of fire; and this last is in turn slave of air, thus completing the circle. The boxed numbers next to each server indicate the server ID, which must be different for each node; the auto_increment_increment, the same for all the nodes; and the auto_increment_offset, which guarantees the uniqueness of self-generated keys.

Here is the complete setup for all nodes:

# node A - water
[mysqld]

Increase innodb_log_file_size

If you modify the innodb_log_file_size, MySQL will fail to restart and InnoDB will complain about the size of the changed log file.

The proper way to increase the innodb_log_file_size:
shutdown mysql server
make backup of data and log files
remove InnoDB log files
set new value for innodb_log_file_size in my.cnf
start mysqld
check error logs to ensure everything went fine.

Host 'hostname' is blocked because of many connection errors

Host 'hostname' is blocked because of many connection errors.Unblock with 'mysqladmin flush-hosts'

проблема в том, что было слишком много ошибок при подключении к mysqld с этого хоста.
возможн это были прерваные запросы. поэтому мускул решил что это атака и заблокировал доступ этому хосту, пока не выполнишь команду mysqladmin flush-hosts
по дефолту значение стоит равное 10
его можно увеличить следующим образом

# safe_mysqld -O max_connect_errors=100 &

либо внести в конфиг my.cnf в секцию [mysqld]:
max_connect_errors = 100

Полезные запросы в БД UTM5

сменить дату окончания расчетного периода на час вперед у тех РП которые заканчиваются в 00 часов

UPDATE discount_periods SET end_date = 
UNIX_TIMESTAMP(DATE_SUB(FROM_UNIXTIME(end_date),INTERVAL -1 HOUR)) WHERE 
discount_periods.is_expired = 0 AND 
HOUR(FROM_UNIXTIME(discount_periods.end_date)) = 0;

сменить дату окончания расчетного периода на час назад у тех РП которые заканчиваются в 01 часов

UPDATE discount_periods SET end_date = 
UNIX_TIMESTAMP(DATE_SUB(FROM_UNIXTIME(end_date),INTERVAL 1 HOUR)) WHERE 
discount_periods.is_expired = 0 AND 

How SHOW SLAVE STATUS relates to CHANGE MASTER TO


When you’re using CHANGE MASTER TO to set start position for the slave you’re specifying position for SQL thread and so you should use Relay_Master_Log_File:Exec_Master_Log_Pos.

http://www.mysqlperformanceblog.com/2008/07/07/how-show-slave-status-rel...