《MySQL Admin Cookbook》笔记

Tuesday, April 27, 2010

Adding indexes to tables

ALTER TABLE books ADD INDEX IDX_author(author), ADD INDEX IDX_title(title);
ALTER TABLE books ADD INDEX IDX_title(title(20)); -- tell MySQL to only copy the first 20 characters of the title to the index:

Adding a fulltext index

ALTER TABLE posts ADD FULLTEXT INDEX IDX_content(content);

Removing indexes from tables

ALTER TABLE books DROP INDEX IDX_author;
ALTER TABLE books DROP INDEX IDX_author, DROP INDEX IDX_title;

Speeding up searches for (sub)domains

UPDATE clients SET maildomain=REVERSE(maildomain);
ALTER TABLE clients ADD INDEX IDXR_MAILDOMAIN(maildomain);
Change all queries in your application as follows:
Before:
SELECT name, maildomain FROM clients WHERE maildomain LIKE '%@gmail.com';
After:
SELECT name, REVERSE(maildomain) AS maildomain FROM clients WHERE maildomain LIKE REVERSE('%@gmail.com');

Displaying query results page by page and with scrolling using the
MySQL command-line client

#set pager
pager less -SFX
SELECT * FROM recipes;
unset pager
nopager

Specifying a default pager

vim my.cnf (edit my.ini in windows OS) in [mysql] section insert this
:

[mysql] pager = less -SFX

Using a custom prompt to distinguish connections

vim my.cnf (edit my.ini in windows OS) in [mysql] section insert this
:

[mysql] prompt = h/u:[d] > _ ps:

h The name of the host you
are connected to u Your username

d The name of the current default
database _ A space character

like this: $ mysql -uroot -p health_development
localhost/root:[health_development]>

Backing Up

mysqldump -uroot -padmin blog | gzip --fast >/var/backups/db/blog.sql.gz

for I in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $I | gzip > "$I.sql.gz"; done


for db in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $db | gzip > "/backups/mysqldump-$(hostname)-$db-$(date +%Y-%m-%d-%H.%M.%S).gz"; done

Performing a point-in-time recovery using the binary logs

CHANGE MASTER TO MASTER_LOG_FILE='myhost.000005', MASTER_LOG_POS=201;

Inserting new data and updating data if it already exists

mysql> INSERT INTO sample.config VALUES ("maxPriceDiscount", "25%") ON DUPLICATE KEY UPDATE value='25%';

Checking free InnoDB tablespace

SELECT DATA_FREE/(1024*1024) AS FREE_MB, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE="InnoDB";

Exporting data to a simple CSV file

SELECT * FROM sample.table1 INTO OUTFILE '/tmp/target.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY 'rn';


echo "SELECT * FROM table; " | mysql -u root -p${MYSQLROOTPW} databasename | sed 's/t/","/g;s/^/"/;s/$/"/;s/n//g' > outfile.csv

Importing data from a simple CSV file

mysql> LOAD DATA INFILE 'C:/source.csv' INTO TABLE sample.table1 FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY 'rn';

Checking free InnoDB tablespace

SELECT DATA_FREE/(1024*1024) AS FREE_MB, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE="InnoDB";

Estimating tablespace requirements

two tables:

CREATE TABLE table1 (
id LONG,
name varchar(255) DEFAULT NULL,
INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE table2 (
name char(16) NOT NULL,
description varchar(128) NOT NULL,
PRIMARY KEY (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Calculate the tablespace requirements for each table by using the
following statements:

mysql> SELECT 1000000 * (DATA_LENGTH + INDEX_LENGTH) /
-> (SELECT COUNT() FROM sample.table1) / (10241024)
-> AS REQUIRED_SPACE_MB
-> FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA="sample" AND
-> TABLE_NAME="table1";
mysql> SELECT 20000 * (DATA_LENGTH + INDEX_LENGTH) /
-> (SELECT COUNT() FROM sample.table2) / (10241024)
-> AS REQUIRED_SPACE_MB
-> FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA="sample" AND
-> TABLE_NAME="table2";

Identifying and changing MySQL variables

mysql> SHOW VARIABLES;
mysql> SHOW VARIABLES LIKE "version";
mysql> SHOW VARIABLES LIKE "version%";
To modify a variable for your connection only
mysql> SET auto_increment_increment=2;
To globally modify a variable, use set SET GLOBAL statement
mysql> SET GLOBAL auto_increment_increment=3;
Displaying more than one named variable at a time
mysql> SHOW VARIABLES WHERE variable_name IN ('wait_timeout', 'autocommit')
         -> OR variable_name LIKE 'version%';

Assessing the overall table count

mysql> SELECT TABLE_SCHEMA, COUNT(*) AS TABLE_COUNT
        -> from INFORMATION_SCHEMA.TABLES
        -> GROUP BY TABLE_SCHEMA WITH ROLLUP;

Finding the biggest tables

mysql> SELECT TABLE_SCHEMA,
        -> TABLE_NAME,
        -> (INDEX_LENGTH+DATA_LENGTH)/(1024*1024) AS SIZE_MB,
        -> TABLE_ROWS
        -> FROM INFORMATION_SCHEMA.TABLES
        -> WHERE TABLE_SCHEMA NOT IN("mysql", "information_schema")
        -> ORDER BY SIZE_MB DESC;

Finding all columns with a certain name and/or type

mysql> SELECT TABLE_SCHEMA,
        -> TABLE_NAME,
        -> COLUMN_NAME,
        -> DATA_TYPE,
        -> CHARACTER_MAXIMUM_LENGTH AS SIZE
        -> FROM INFORMATION_SCHEMA.COLUMNS
        -> WHERE COLUMN_NAME="name" AND
        -> TABLE_SCHEMA NOT IN ("mysql", "information_schema");
To find all columns with data type VARCHAR(64),
mysql> SELECT TABLE_SCHEMA,
        -> TABLE_NAME,
        -> COLUMN_NAME,
        -> DATA_TYPE,
        -> CHARACTER_MAXIMUM_LENGTH AS SIZE
        -> FROM INFORMATION_SCHEMA.COLUMNS
        -> WHERE DATA_TYPE="VARCHAR" AND
        -> CHARACTER_MAXIMUM_LENGTH=64 AND
        -> TABLE_SCHEMA NOT IN ("mysql", "information_schema");

Finding all tables referencing each other

mysql> SELECT TABLE_NAME,
    -> CONSTRAINT_NAME,
    -> UPDATE_RULE AS "UPDATE",
    -> DELETE_RULE AS "DELETE"
    -> FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
    -> WHERE REFERENCED_TABLE_NAME="employees";

SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables WHERE engine="InnoDB";

innodb_data_file_path = ibdata1:10M:autoextend

innodb_file_per_table= 1

ALTER TABLE example_table ENGINE=InnoDB;

 Enabling and configuring binary logging
    [mysqld]
    log_bin=/var/log/mysql/HOSTNAME-bin
    expire_logs_days=10
    max_binlog_size=200M

 configuring important MySQL and InnoDB timeout options
    [mysqld]
    innodb_lock_wait_timeout=50
    interactive_timeout=1200
    wait_timeout=28800
    net_read_timeout=30
    net_write_timeout=120

lower_case_table_names = 1

Running multiple MySQL server instances in parallel on a Linux
server

How to do it...

1. Locate and open the my.cnf configuration
file in a text editor. 
2. Create the following two sections in the file:
# mysqld_multi test, instance 1 [mysqld1] server-id=10001
socket=/var/run/ mysqld/mysqld1.sock 
port=23306 
pidfile=/
var/run/mysqld/mysqld1.pid 
datadir=/var/lib/mysql1 log_bin=/var/
log/mysql1/mysql1-bin.log # mysqld_multi test, instance 2 [mysqld2]
server-id=10002 
socket=/var/run/ mysqld/mysqld2.sock 
port=33306
pidfile=/ var/run/mysqld/mysqld2.pid 
datadir=/var/lib/mysql2
log_bin=/var/ log/mysql2/mysql2-bin.log 
3. Save the configuration file.
4. Issue the following command to verify the two sections are found by
mysqld_multi: $ sudo mysqld_multi report 
5. Initialize the data
directories: ``$ sudo mysql_install_db --user=mysql
--datadir=/var/lib/mysql1 $ sudo mysql_install_db --user=mysql
--datadir=/var/lib/mysql2``
 6. Start both instances and verify they have
been started: $ sudo mysqld_multi start 1 $ sudo mysqld_multi report

7. Connect to both instances and verify their settings:

    $ mysql -S/var/run/mysqld/mysql1.sock 
    mysql> SHOW VARIABLES LIKE 'server_id'; 
    $
    mysql -S /var/run/mysqld/mysql2.sock 
    mysql> SHOW VARIABLES LIKE 'server_id';

GRANT

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, LOCK
TABLES, EXECUTE ON sample.* TO 'sample_stduser'@'%' IDENTIFIED BY
'S4mpl3-Pw';
grant ALL PRIVILEGES on sample.* to 'sample_install'@'%' identified by
'1n5t4ll-Pw';
GRANT ALL PRIVILEGES ON sample.* TO 'sample_install'@'%' IDENTIFIED BY
'1n5t4ll-Pw' WITH GRANT OPTION;
GRANT SELECT ON sample.* TO 'sample_guest'@'%' IDENTIFIED BY
'R34d-0nly';
GRANT SELECT, LOCK TABLES, RELOAD, REPLICATION CLIENT ON . TO
'backup_user'@'localhost' IDENTIFIED BY 'B4ckM3Up!';
GRANT REPLICATION SLAVE ON . TO 'repl_user'@'bluebox' IDENTIFIED BY
'Pw_4_R3pl';
SET PASSWORD FOR 'admin4mysql'@'localhost' =
PASSWORD('As,ysp4M');
GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' IDENTIFIED
BY 'r00t_pw' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON . TO 'admin4mysql'@'localhost' IDENTIFIED BY
'As,ysp4M' WITH GRANT OPTION;
SELECT PASSWORD('As,ysp4M');

download MySQL Admin Cookbook
pdf

This entry was tagged MySQL and tips

comments powered by Disqus

© 2009-2013 lxneng.com. All rights reserved. Powered by Pyramid

go to Top