Notes‎ > ‎

MySQL

The gadget spec URL could not be found
The gadget spec URL could not be found

Create MySQL database

create database x;
grant all on x.* to x@localhost identified by 'Nothing69';

MySQL has gone away

Increase max_allowed_packet

Edit C:\xampplite\mysql\bin\my.ini (Windows)

[mysqld]
max_allowed_packet = 100M # Default: 1M

Restart MySQL service

Try re-importing the SQL dump
mysql -p[password] -D[database name] < dump.sql

Optimizing all Tables

./mysqlfragfinder.sh --user root

Locking

If your database stop from locked threads, see

Suggestions

(1) Set the low_priority_updates server system variable equal to 1.

mysql -p
show variables like 'low_priority_updates';
set low_priority_updates=1;
show variables like 'low_priority_updates';

If that doesn't solve it, then

(2) Convert all tables to InnoDB which does row-level locking. MyISAM does table-level locking

Query cache


Query cache variables
show variables like 'query%';

Query cache performance
show status like 'qc%';

Managing the Query Cache
There are times when you may have to tweak the MySQL query cache to ensure optimal performance, so let's review some of the more important query cache-related status counters and configuration variables. To begin, the status counter Qcache_free_blocks indicates the contiguous nature of the memory assigned to the cache. High numbers can indicate fragmentation issues, which may be solved by issuing a FLUSH QUERY CACHE statement. Note that this command does not remove queries from the cache, but coalesces memory free space chunks.

GoDaddy settings

To see what GoDaddy uses for their MySQL server settings, use PuTTY to SSH to your terminal

eg
thegamenet.com

mysql --host=$your-database-host -p -u$your-database-user

eg
mysql --host=tgn1022209371016.db.4769787.hostedresource.com -p -utgn1022209371016

Commands


Dump database

# TGN Games on GoDaddy
mysqldump -htgn1028404331273.db.4769787.hostedresource.com -utgn1028404331273 -p$password tgn1028404331273 > tgngames-new.sql

# FTP to new server
wget ftp://aionpress:$password@thegamenet.com/tgngames-new.sql

# Import to new database
mysqladmin create $db -p$password && cat tgngames-new.sql | mysql -p$password $db

Duplicate database

mysqlhotcopy -user=root --password=$password $fromDatabase $toDatabase

or (slower way, can lose connection to MySQL database if it takes too long)

mysqladmin create $dbcopy -uroot --password=$password && \
mysqldump -uroot --password=5CniirE4 $db | mysql -uroot --password=$password $dbcopy

Move Site to New Host

rsync -z -r -a -v -e "ssh -l anytv" --delete any.tv:/home/anytv/public_html/any.tv/ .

Moves from Vexxhost to Dreamhost (run this when SSH'd into Dreamhost in the destination folder)

Mongo on Dreamhost PHP Shared Host

Excellent tutorial that works!

Comments