Archive for the ‘Database’ Category

Server 2012 R2 – Failover Cluster error

So I was installing SQL2008R2 (In 2015? I know not my choice, it was for a legacy application) into a Windows Server 2012 R2 Failover Cluster. I hit the following error during the SQL install: “Rule Cluster Service Verification Failed. The SQL Server failover cluster services is not online”. It turns out that some cluster […]

Configuring MySQL over SSL for client connections

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 […]

MySQL reset lost root password

/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

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

##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

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

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

# Edit /etc/my.cnf log-queries-not-using-indexes = /var/log/mysql-indexes.log

MySQL set Fulltext Min_Word_Len

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

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