MySQL Resources The current stable version of MySQL is 3.23.52. Version 4.x is the current development release and is not yet considered stable. Both versions can be downloaded from http://www.mysql.com/downloads MySQL Books *** Excellent ** OK * Don’t waste your money *** Beginning Databases with MySQL, by Neil Matthew and Richard Stones. WROX Press. 2002. …
MySQL I : Text Searches
1. Logon to the MySQL Monitor client. 2. Use the accounting database: USE accounting; 3. Make sure the expenses table is populated: SELECT * FROM expenses; 4. Create a full text index: ALTER TABLE expenses ADD FULLTEXT (expense_description); – a full text index must be created on the columns you wish to search. 5. Do …
MySQL I : Table Locking
1. Login to the MySQL Monitor client. 2. Use the “accounting” database: USE accounting; 3. Lock the tables you will be using: LOCK TABLES expenses READ, expense_categories READ; 4. Flush the tables (put table in its most current state): FLUSH TABLES expenses, expense_categories; 5. Backup the tables to your “c:\temp” directory: BACKUP TABLE expenses, expense_categories …
MySQL I : Transactions
1. Login to the MySQL Monitor client. 2. Use the new “movies_db” InnoDB database: USE movies_db; 3. Start a transaction: BEGIN; INSERT INTO directors (first_name, last_name) VALUES (‘Martin’, ‘Scorsese’); INSERT INTO movies (title, director_id) VALUES (‘The Age of Innocence’, LAST_INSERT_ID()); 4. Assuming you typed these correctly you should get no errors. If you get an …
MySQL I : GUI Tools
1. There are a number of excellent graphical MySQL clients that make development and administration of MySQL much easier than if using the MySQL Monitor command line client. The best so far is “MyCC”, though it is still in development. Another, equally functional, but not so well designed is “MySQLGUI”. Both are free open source …
MySQL I : Logging
1. Error logging is enabled by default. With a text editor, examine the file “c:\mysql\data\mysql.err” 2. To enable update tracking, stop the MySQL service or daemon. Windows: – Open Start > Settings > Control Panel > Administrative Tools > Services – Stop the MySQL service – Double click the MySQL service to open its properties. …
MySQL I : Batch Operations and Data Import
1. Create a new database. From the shell prompt, in the bin directory type: mysqladmin -u root -p create accounting2 2. We can use the MySQLDump files as scripts to recreate our tables. These .sql text files are located in the “c:\temp” directory, where we dumped to in the backup exercise. Locate “c:\temp\expenses.sql” and examine …
Continue reading “MySQL I : Batch Operations and Data Import”
MySQL I : Maintenance and Patching
Basic maintenance 1. Back up all data, in case something goes wrong during the repair and optimization process. To do this, stop MySQL, create a fold named <todays_date>, and manually copy the data directory there. Restart MySQL. 2. Determine which tables you want to analyze and repair. 3. cd to the bin directory: cd c:\mysql\bin …
MySQL I : Backing Up
1. Techniques described are the same on Linux/UNIX, Windows, and Mac OS X. 2. One way to find out where your MySQL data files are stored is to look at the MySQL variables. CD to your “bin” directory, and type: mysqladmin –u root –p variables On Windows you can also launch “Winmysqladmin.exe” and click the …
MySQL I : Grouping
4. Grouping functions allow you to group records by category and perform some minimal statistics on the category. For example: to find the maximum and minimum values of a group: SELECT MAX(invoice_amount) FROM invoices; SELECT MIN(invoice_amount) FROM invoices; 5. To determine how much has been spent under each expense category: SELECT SUM(expense_amount), expense_category FROM expenses …