User Tools

Site Tools


mysql

mysql_php_testpage

Change user password:

UPDATE mysql.user SET Password=PASSWORD('cleartext password')
  WHERE User='bob' AND Host='%.example.org';
FLUSH PRIVILEGES;

Show users:

select user,host from user;

Check DB:

# mysqlcheck "databases name" "table name"

mysql> show table status tablename\G

Repair DB:

 mysqlcheck -u root -p -Ao --auto-repair

mysql_slave_stat.sh

mysql -e 'show slave status\G' | egrep 'Slave_|Seconds'

Show how a table was created:

mysql> SHOW CREATE TABLE table_name;

Show table structure:

mysql> desc table_name;

Script to query CORE and report slave status for each ticket in your queue pertaining to MySQL slave_check_q.sh

#!/bin/bash
#Vinny Huckaba July, 1 2012
date=`date`
#Will only check ticket In Progress 21Aug12
COREDEVS=( `core-cli -M | grep -A 1 MyS | grep -B 1 Progress |  cut -c25-30` )

for i in {1..100}
do
echo "Script has run $i times so far with a 100 limit for safety."
echo "Querying CORE for your tickets, please wait..."
for dev in ${!COREDEVS[*]}
do

# begin zero ticket testing
echo "There are ${#COREDEVS[@]} MySQL tickets in core assigned to you."
if [ ${#COREDEVS[@]} -lt 1 ]
then
exit
fi
#end zero ticket test

echo ${COREDEVS[$dev]}
SECONDS=`ht ${COREDEVS[$dev]} --sudo-make-me-a-sandwich -s mysql_slave_stat.sh | tail -c 6`
echo $SECONDS>$dev.mysqlstatus
STATUS=`cat $dev.mysqlstatus`
    printf " %s \n" "Device ${COREDEVS[$dev]} is now $STATUS seconds behind"

        if [ $STATUS -gt 0 ]
        then
echo "Slave is still $STATUS seconds behind, waiting to catch up"
        else
echo " "
echo "It appears this alert has cleared without Rackspace intervention."
echo " "
echo " "
echo "Summary: Device ${COREDEVS[$dev]} slave is $STATUS seconds behind the master at $date"
echo " "
echo " "
echo "Please feel free to update this ticket if you have any questions or concerns."

          mail -s "Device ${COREDEVS[$dev]} is $STATUS behind at $date" your@email.com < ${COREDEVS[$dev]}.mysqlstatus
#working on exit
fi
#Test for zero seconds on last status
done
if [ $STATUS -lt 1 ]
then
exit
fi
echo "Sleeping 5 minutes before checking Slave Status again"
sleep 300
done

Adding DB and User:

$ mysql -u adminusername -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5340 to server version: 3.23.54
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> CREATE DATABASE databasename;
Query OK, 1 row affected (0.00 sec)
 
mysql> GRANT ALL PRIVILEGES ON databasename.* TO "username"@"hostname"
    -> IDENTIFIED BY "password";
Query OK, 0 rows affected (0.00 sec)
  
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> EXIT
Bye
$ 

Slave Status:

watch "mysql -e 'show slave status\G' |egrep 'Slave|Seconds'"

 mysql -e 'show slave status\G' |egrep 'Slave|Seconds'

Status:

 mysql -e 'status;' &&  mysql -e 'show processlist;' | grep -v Sleep && echo "--------------"; w

Shwo Users:

mysql -e 'select * from mysql.user\G;' | grep User

select * from mysql.users \G;

Use this command before you dump or snapshot mysql to lock all tables:

FLUSH TABLES WITH READ LOCK;

#mysql show DB sizes:

SELECT table_schema "Data Base Name", SUM( data_length + index_length) / 1024 / 1024 
"Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;
mysql -e 'show full processlist\G'

Show Slave Status from Linux commandline:

mysql -e 'show slave status\G;'

Test mysqldump for end size and crunch rate using pv:

 mysqldump -u root -p --all-databases --single-transaction | gzip | pv -btr > /dev/null

Process List:

mysqladmin processlist | cut -d"|" -f3 | sort | uniq -c | sort -n | tail -n 8

Engine:

mysql -e "show engines;"

Show Corrupt Tables:

mysqlcheck -Asc

Slow Query Log

Check if slow log is enabled:

mysqladmin var | grep slow

mysqldumpslow slow-log

touch /var/lib/mysql/slow.log chown mysql. /var/lib/mysql/slow.log

In the my.cnf file under the mysqld section add this:

log-slow-queries=/var/lib/mysql/slow.log

Then restart mysql and you have a slow query log.

If you want to specify the number of seconds that indicates a long or slow query, use this line in /etc/my.cnf :

long_query_time = 5

Master Slave:

What I've done:
1. on master box (note I did not try --master-data)
mysql> flush tables with read lock;
mysql> show master status\G;

$ mysqldump -u root -A > all_database.mysql
mysql> unlock tables;

2. copy to slave box
3. on slave box
mysql> stop slave; reset slave;
$ mysql -u root < all_database.mysql
4. change master to ... (details from show master status above)
5. mysql> start slave 

MySQl slave error skip to continue:

First try (on the slave):
SLAVE STOP; SLAVE START;
SHOW TABLE STATUS\G

If the problem is still there, then do
SLAVE STOP; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
That should skip over the offending statement (without figuring out why it was a problem.)

Increase Max Connections:

SET GLOBAL max_connections = 500;

Adding Users:

$ mysql -u adminusername -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5340 to server version: 3.23.54
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> CREATE DATABASE databasename;
Query OK, 1 row affected (0.00 sec)
 
mysql> GRANT ALL PRIVILEGES ON databasename.* TO "username"@"hostname"
    -> IDENTIFIED BY "password";
Query OK, 0 rows affected (0.00 sec)
  
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> EXIT
Bye
$ 

Slave replication:

On master:
my.cnf:

===========================

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#master Setup
log-bin=mysql-bin
server-id=1
#Recommended:
innodb_flush_log_at_trx_commit=1
sync_binlog=1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
=========================== 

mysql> grant replication slave on *.* to slaveuser@'slaveip' identified by 'slavepw';
mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status;

# mysqldump -u root -p -A --master-data > /root/mysqldump.sql

mysql> UNLOCK TABLES;

Copy mysqldump to slave

Slave:

# mysql -uroot -p < mysqldump.sql

mysql> CHANGE MASTER TO MASTER_HOST='10.179.47.138', MASTER_USER='replicator', MASTER_PASSWORD='somepasswd', MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=1792151;
mysql> start slave;
mysql> show slave status\G;

Note: Shut the slave process down before locking the tables on the master.

If you are just updating date from a dump due to a slave failure or other, use the following commands to set the binlog and position.

mysql> CHANGE MASTER TO MASTER_LOG_FILE='binlog.000xxx';

mysql> CHANGE MASTER TO MASTER_LOG_POS=xxxxxxxx;

Slave to master:

Recipe to promote a slave to a master for simple replication.

On Master:

    FLUSH LOGS;

On Slave:

    STOP SLAVE;
    RESET MASTER;
    Optional: CHANGE MASTER TO MASTER_HOST='';

Check slave:

mysqladmin proc stat

for i in {1..10}; do ll mysql.log.$i; cat mysql.log.$i; echo "**************************************************************"; done

Update info:

mysql> update db set Host='8.8.8.8' where Db='webdb';
mysql> update user set Host='8.8.8.8' where user='webadmin';

Turn off warning logging:

set global log_warnings=0;
mysql> show variables like '%warning%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings  | 0     |

Purging old bin-log files:

mysql -e "PURGE BINARY LOGS TO 'bin-log.005';"

mysql -e 'set global expire_logs_days=2;'

mysql -e 'show variables;' | grep -i expire
mysql.txt · Last modified: 2015/04/21 05:35 by vinny