MySQL

MySQL Common Queries

0
2 years ago
by Mike Purcell in

Came across an awesome site where the author posts queries for a lot of common data retrieval scenarios. Definitely worth a look if hazy on more complex types of queries.

Check it out @ www.artfulsoftware.com

MySQL Add Columns

0
2 years ago
by Mike Purcell in

Add a column with no regard for order of entry among other columns:

ALTER TABLE `table_name` ADD COLUMN `column_name` int(11) unsigned not null default 0;

Add a column, but specify that it should be the first column of the table:

ALTER TABLE `table_name` ADD COLUMN `column_name` int(11) unsigned not null default 0 FIRST;

Add a column, but specify it's location other than first:

ALTER TABLE `table_name` ADD COLUMN `column_name` int(11) unsigned not null default 0 AFTER `some_other_column`;

MySQL Tuning Primer Script

0
2 years ago
by Mike Purcell in

Suggested from #MySQL on irc.freenode.net, this appears to be a very useful utility for performance tuning your MySQL database. I haven’t used it yet, but plan too on the next project.

MySQL Tuning Primer Script

MySQL – Import data from .sql file

0
2 years ago
by Mike Purcell in

The following command will import schema and/or data from a mysqldump file:

mysql -h <host_name> <db_name> -u <username> -p < <file_name>.sql

MySQL – Resetting auto_increment counts

0
2 years ago
by Mike Purcell in

There may be times where you need to reset the auto_increment counter of a MySQL table, here’s how:

ALTER TABLE <table_name> AUTO_INCREMENT = 1;

MySQL DB Dump

0
2 years ago
by Mike Purcell in

MySQL provides a way to get a snapshot of your schema via mysqldump. Care needs to be taken though, if your tables are large you are better off with a select into outfile or some sort of replication and running the command from a low traffic server b/c it will lock your tables for as long as it takes to grab the data.

mysqldump -u <user_name> -h <db_host> --opt <database_name> -d --single-transaction > backup.sql

Mysql – Granting permissions via Command Line Interface

0
2 years ago
by Mike Purcell in

Needed to do this to setup a MySQL server on my local machine to facilitate unit test testing…  I used XAMPP and started the MySQL app. After it starts open your terminal and type the following:

mysql -u root
mysql: GRANT ALL ON *.* TO '<some_user>'@'%' IDENTIFIED BY '<some_password>'
mysql: FLUSH PRIVILEGES;
mysql: EXIT

mysql -u <some_user> -p<some_password>
mysql: exit

You should now be able to connect to your local mysql server from any host using the supplied name and password.

Go to Top