mysql
show processlist; kill id;
◆旧 /usr/bin/mysqldump -hdb_host -udb_user -pdb_pass db_name table_name > /tmp/table_name.stdout.sql 2> /tmp/table_name.stderr.txt → エラーが出力される場合もある Warning: Using a password on the command line interface can be insecure. ◆新 …
SELECT ROUND(SUM(data_length)/1024/1024) AS mb FROM information_schema.tables WHERE table_schema = 'aaa' ;
SELECT auto_increment FROM information_schema.tables WHERE table_name = 'aaa' ; ALTER TABLE aaa AUTO_INCREMENT = 1 ;
mysqldump -hhostname -uusername -ppassword dbname table1 table2 table3 > dump.sql 2> err.txt
charset utf8;
SELECT table_name FROM information_schema.tables WHERE table_type = 'VIEW';
-- datetime to unixtimestamp SELECT UNIX_TIMESTAMP('2016-12-31 23:59:59'); -- unixtimestamp to datetime SELECT FROM_UNIXTIME(1483196399);
CREATE DATABASE d_name CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ;
SET @nummin:=1; SET @nummax:=999999999999999999; SELECT LPAD( FLOOR( @nummin + (RAND() * (1 + @nummax)) ) ,CHAR_LENGTH( CAST( @nummax AS CHAR ) ) ,'0' ) AS rand_num ;
SELECT user, host FROM mysql.user; DROP USER 'aaa'@'localhost';
SELECT CAST(NOW() AS UNSIGNED);
SHOW INNODB STATUS\G
SELECT bbb_id INTO OUTFILE 'c:/bbb_isnotnull_list.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM v_tmp WHERE bbb_id IS NOT NULL ;
CREATE VIEW v_tmp AS SELECT t_aaa.id AS aaa_id ,t_bbb.id AS bbb_id FROM t_aaa LEFT JOIN t_bbb ON t_aaa.id = t_bbb.id ;
SELECT * FROM information_schema.statistics ;
ALTER TABLE 【テーブル名】 ENGINE = 【エンジン】; ANALYZE TABLE 【テーブル名】; MySQL :: MySQL 5.1 リファレンスマニュアル (オンラインヘルプ) :: 8.1.7 ALTER TABLE 構文 ALTER TABLE ステートメントのあとに、インデックスカーディナリティー情報を…
SELECT table_name, engine FROM information_schema.tables ORDER BY table_name ASC ;
SELECT schema_name FROM information_schema.schemata ORDER BY schema_name ASC ;
SHOW status; SHOW variables;
SET GLOBAL slow_query_log = 1; SET GLOBAL long_query_time = 5; SET GLOBAL log_queries_not_using_indexes = 1; SET GLOBAL slow_query_log_file = '/log_file_path/slow.log';
※以下の様に読み替えてください。 aaaaaa: root passwd bbbbbb: dbuser name cccccc: dbuser passwd dddddd: database name eeeeee: table name 【rootのパスワード設定】 ■rootユーザでログイン(パスワードなし) /usr/local/mysql/bin/mysql -hlocalhost …