mysql tips

Убить процесс

mysql
mysql> show processlist;
mysql> kill THREAD_ID;

cat www/lab/site/catalog/files/catalog.txt|awk '{printf ("insert into lab_catalog(name) values\(-nnn-%s-nnn-\);\n",$0);}'|sed 's/\"/\\"/g;s/-nnn-/"/g'|mysql -u USER -h HOST NAME_OF_DB -pPASSWD
echo "drop table jos_templates_menu;"|mysql -u USER -h HOST NAME_OF_DB -pPASSWD

#изменить параметры столбца
mysql> alter table srvtype modify column stypeid int not null auto_increment ; 

#переименовать таблицу
mysql> rename table gamedb.stype to gamedb.game;

#переименовать столбец
mysql> alter table stype change column stypeid gameid int ;

#удалить столбец
mysql> alter table ptemplates drop column pttid;

#поменять пароль root
update user set password=PASSWORD('123') where user='root';

#добавить столбец
mysql> alter table srv add column tsrvid int not null after tid ;
create table lev_dir (did int NOT NULL PRIMARY KEY auto_increment,dir varchar(255) NOT NULL);
create table lev_timeline (id int NOT NULL PRIMARY KEY auto_increment,post varchar(255) NOT NULL, time timestamp,description blob NULL);
select UNIX_TIMESTAMP(time) from lev_log where description='last_update_timeline';
create table lab_catalog (cid int NOT NULL PRIMARY KEY auto_increment,name varchar(255) NOT NULL, description blob NULL, image blob NULL);
select count(8) from lev_timeline;
mysql> show tables;;
+-------------------+
| Tables_in_u192625 |
+-------------------+
| lab_catalog       | 
| lab_dir           | 
| lab_timeline      | 
| lev_dir           | 
| lev_log           | 
| lev_timeline      | 
+-------------------+
6 rows in set (0.00 sec)

ERROR: 
No query specified

# dump базы без данных
[u192625@be126 ~]$ mysqldump -u USER -d -h HOST NAME_OF_DB -pPASSWD > dbdump.$(date +%s)
# полный dump базы
[u192625@be126 ~]$ mysqldump -u USER -h HOST NAME_OF_DB -pPASSWD > dbdump.$(date +%s)

# restore базы
mysql --verbose --user=u192625 --password=XXXXX u192625 < /opt/u192625/dbdump.1264062119

# проверка целостности
/usr/local/mysql50/bin/myisamchk --check /db/mysql50/u166171/*.MYI

# создание пользователя, таблицы для него и добавление прав на таблицу
mysql -u root mysql
mysql> CREATE USER 'u192625'@'localhost' IDENTIFIED BY 'XXXXX';
Query OK, 0 rows affected (0.00 sec)

mysql> create database u192625;
Query OK, 1 row affected (0.08 sec)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on u192625.* to 'u192625'@'localhost';
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE USER 'u192625'@'%' IDENTIFIED BY 'XXXXX';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on u192625.* to 'u192625'@'%';
Query OK, 0 rows affected (0.00 sec)

или для mysql 4.0

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on game.* to 'admin'@'%' IDENTIFIED BY 'xxxx' WITH GRANT OPTION;

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
если прав нужно немного побольше делаем 
mysql> insert into db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Index_priv, Alter_priv, Lock_tables_priv) values ('%', 'u192625', 'u192625', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# удаление пользователя
mysql> drop user 'custom'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

# удаление привилегий пользователя
mysql> delete from db where Db = 'u192625';
Query OK, 1 row affected (0.00 sec)

# удаление таблицы
mysql> drop table lab_catalog;
Query OK, 0 rows affected (0.00 sec)

# Восстановление баз
описано простой функцией

repair () {
  /usr/local/bin/mysqlshow ${db} no_auto_repair %> /dev/null 2>&1
  if [ $? -eq 1 ]; then
        if [ "x${db}" != "x" -a -d ${DATADIR}/${db} ]; then
                echo "Repairing database '${db}'"
                echo "show table status" | ${PREFIX}/bin/mysql ${db} | grep crashed | awk '{print $1}' |
                while read table; do
                   echo "  Table '"${table}"'";
                   echo "REPAIR TABLE ${table} EXTENDED" | ${PREFIX}/bin/mysql -u root ${db};
                   echo "show table status  where Name = '${table}'" | ${PREFIX}/bin/mysql -u root ${db} | grep crashed >&2;
        done

        else
                echo "Error: can't find database '${db}'"
        fi 
  else
          echo "Table no_auto_repair exists in this database ${db}"
 fi
}

  Разбор полетов

Ошибка

mysql> CREATE USER 'dbmail'@'%' IDENTIFIED BY 'xxx-dbmail-xxx';
ERROR 1396 (HY000): Operation CREATE USER failed for 'dbmail'@'%'

Ответ

mysql> drop user dbmail;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Настройка long_query_time

mysqladmin -uUSER -hHOST -pPASS variables | grep long_query_time
| long_query_time                 | 3