《mysql admin cookbook》notes

gistfile1.txt

Chapter 2 Indexing


	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');


Chapter 3: Tools

	Displaying query results page by page and with scrolling using the MySQL command-line client
			#set pager
			mysql> pager less -SFX
			PAGER set to 'less -SFX'
			mysql> SELECT * FROM recipes;

			#unset pager
			mysql> nopager
			PAGER set to stdout
			
	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: $ mysql -uroot -p health_development
					   localhost/root:[health_development]>
			

Charpter 4: Backing Up and Restoring MySQL Data
	
	MySQLDump
			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;

Chapter 5: Managing Data
 	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 '\r\n';
           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 '\r\n';

	
Chapter 6: Monitoring and Analyzing a MySQL Installation

	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) / (1024*1024)
				-> 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) / (1024*1024)
				-> 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";
			
Chapter 7: Configuring MySQL

	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';

Chapter 8: MySQL User Management

	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');