Useful for setting up encrypted connections between client and server. Parts of this can be substituted if you wished to get a certificate issued from a trusted CA. 1. Check that SSL has been compiled in MySQL on the server: SHOW VARIABLES LIKE ‘have_openssl’; or mysql –ssl –help If it says disabled or yes then [...]
Archive for the ‘Database’ Category
MySQL reset lost root password
December 23rd, 2009
No Comments
/etc/init.d/mysql.server stop /usr/local/mysql/bin/safe_mysqld –user=mysql –skip-grant-tables –skip-networking & /usr/local/mysql/bin/mysql mysql> UPDATE mysql.user SET Password=PASSWORD(‘MyNewPass’) WHERE User=’root’; mysql> FLUSH PRIVILEGES; mysql> exit /etc/init.d/mysql.server restart Probably a good idea to clear the MySQL history afterwards or pull in the update from a txt file and delete!
Purge MySQL Binary Logs
December 22nd, 2009
No Comments
If you have binary logging enabled in MySQL, for example if you are using replication, there is a good chance that these binary logs will grown pretty big and fill the disk. It’s a good idea to set some limits in my.cnf expire-logs-days = 20 max_binlog_size = 104857600 If you find the need to purge [...]
MySQL Replication over SSL
December 21st, 2009
No Comments
##Tutorial for MySQL Replication over SSL (one-way)## ##################################################### ##Check that SSL has been compiled in SSL on both the master and the client: SHOW VARIABLES LIKE ‘have_openssl’; or mysql –ssl –help If it says disabled or yes then its fine. If it says no then an SSL enabled version of MySQL needs to be installed. [...]
MySQL repair corruption
December 21st, 2009
No Comments
for db: mysqlcheck -uadmin -p$(cat /etc/psa/.psa.shadow ) –databases databasename –repair for table: check table tablename; repair table tablename; repair can lose data so a restore from backup may be necessary.
MySQL Log slow queries
December 21st, 2009
No Comments
edit /etc/my.cnf: log-slow-queries=/var/log/mysql-slow.log set-variable=long_query_time=6 restart mysqld
MySQL Log queries not using indexes
December 21st, 2009
No Comments
# Edit /etc/my.cnf log-queries-not-using-indexes = /var/log/mysql-indexes.log
MySQL set Fulltext Min_Word_Len
December 21st, 2009
No Comments
add the following to my.cnf under [mysqld] ft_min_word_len=2 stop mysql myisamchk –recover –ft_min_word_len=2 /var/lib/mysql/*/*.MYI start mysql
MySQL Dump database to Email
December 21st, 2009
No Comments
To backup a database and send it to email, add a cron job like: mysqldump -ce –user=user –password=pass dbname | gzip |uuencode dbbackup.gz| mail -s “xxxx Database Backup” me@mydomain.com
MSSQL Express Scheduled Backups
December 21st, 2009
No Comments
The Express edition of SQL server lacks automated scheduled backup functionality. The following script will back up your databases. It retains them for 7 days but can be easily edited: Create a directory called c:\SQLBackup and go into it Create a backup.sql file: DECLARE @name VARCHAR(50) — database name DECLARE @path VARCHAR(256) — path for [...]
