MySQL
MYSQL
This is the Mysql shit page.
Mysql Uptime and Mysql status
Uptime
mysql -e "status;" | grep Uptime
Full Status
mysql -e "status;"
Find avtual max connections (what it is currently set to)
mysql -e "show variables;" | grep max_connections
Show all mysql variables
mysql -e "show variables;"
Mysql tools
Mysql report
http://hackmysql.com/mysqlreport
install as folows.
cd /usr/local/src/ wget http://hackmysql.com/scripts/mysqlreport-3.5.tgz tar -zxvf mysqlreport-3.5.tgz cd mysqlreport-3.5 ./mysqlreport
Mysql tuner
THE optimization script.
wget -O /scripts/mysqltuner.pl http://mysqltuner.com/mysqltuner.pl chmod +x /scripts/mysqltuner.pl /scripts/mysqltuner.pl
Tuning Primer
Yet another optimization script.
wget -O /scripts/tuning-primer.sh http://day32.com/MySQL/tuning-primer.sh chmod +x /scripts/tuning-primer.sh /scripts/tuning-primer.sh
Changing ENGINEs (innodb and myisam)
Change out the relavent information below.
mysql -D dbname -e "ALTER TABLE \`dbtable\` ENGINE = InnoDB or MYISAM;"
Backing stuffs up
To backup all databases to remote location
mkdir /home/sqlbackup for db in `mysql -s -B -e "show databases"`;do mysqldump $db > /home/sqlbackup/$db.sql.(version number here);done
Repairing and Defragmenting
To repair a crashed MYISAM table stop chkservd and mysql first.
/usr/local/cpanel/bin/tailwatchd --disable=Cpanel::TailWatch::ChkServd /etc/init.d/mysql stop
Then You will need to run.
myisamchk -r /var/lib/mysql/(databasename)/(tablename) /etc/init.d/mysql start /usr/local/cpanel/bin/tailwatchd --enable=Cpanel::TailWatch::ChkServd
Resetting the Root Password: Generic Instructions
The preceding sections provide password-resetting instructions for Windows and Unix systems. Alternatively, on any platform, you can set the new password using the mysql client (but this approach is less secure):
1. Stop mysqld and restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges. Because this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting.
/etc/init.d/mysql stop /etc/init.d/mysql start --skip-grant-tables --skip-networking
2. Connect to the mysqld server with this command:
mysql
3. Issue the following statements in the mysql client. Replace the password with the password that you want to use.
UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;
The FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change. You should now be able to connect to the MySQL server as root using the new password. Stop the server, then restart it normally (without the --skip-grant-tables and --skip-networking options).
General MySQL Fixes
"MySQL server has gone away" fix is to increase these two settings, usually by doubling them.
wait_timeout max_allowed_packet
You will find these in the my.cnf.
vim /etc/my.cnf
When you have completed that Restart MySQL.
/etc/init.d/mysql stop /etc/init.d/mysql start
Correct way to upgrade mysql in cpanel box
Firstly Check the versions currently installed (after the upgrade easy apache wont report the php version so we do this first.)
php -v mysqladmin version
Back up the databases before anything else.
mkdir /home/sqlbackup for db in `mysql -s -B -e "show databases"`;do mysqldump $db > /home/sqlbackup/$db.sql.`date +%Y%m%d%I`;done
Change mysql-version to the version you want.
vim /var/cpanel/cpanel.config
change mysql-version to what you want (IE...4.1, 5.1)) Or I have a copy paste clicky here for it.
To do 4.1 to 5.0
sed -i -e 's#mysql-version=4.1#mysql-version=5.0#g' /var/cpanel/cpanel.config
To do 5.0 to 5.1
sed -i -e 's#mysql-version=5.0#mysql-version=5.1#g' /var/cpanel/cpanel.config
To do 4.1 to 5.1 (worst idea ever)
sed -i -e 's#mysql-version=4.1#mysql-version=5.1#g' /var/cpanel/cpanel.config
/usr/local/cpanel/whostmgr/bin/whostmgr2 --updatetweaksettings
/scripts/mysqlup --force mysqladmin version
/scripts/easyapache
Other Mysql stuffs
Myqsl packages are installed
rpm -qa | grep SQL | grep -vi perl
Force perl update
/scripts/perlinstaller --force Bundle::DBD::mysql
what mysql version
mysqladmin version
Mysql database location
cd /var/lib/mysql
imports a dumped file
mysql database < db-dump-file.sql
Mysqldump
mysqldump dbname > dbname.sql
Export all databases to a single sql file
mysqldump -u root -p --all-databases > all.sql
Import all databases back to mysql.
mysql -u root -p < all.sql
/scripts/mysqlup --force fails
The first thing you should try in this instance is checking for a bad configuration file.
updatedb& locate mysql_config
From what I have seen this can be located in a few directories.
/usr/bin/mysql_config /usr/local/bin/mysql_config
From what I learned in a recent cpanel ticket, /usr/bin/mysql_config is the current correct location for this file. Back up any of these files found and then remove them and try the build again.
cp /usr/bin/mysql_config /usr/bin/mysql_config.bak cp /usr/local/bin/mysql_config /usr/local/bin/mysql_config.bak rm /usr/local/bin/mysql_config /usr/bin/mysql_config /scripts/mysqlup --force
If that doesnt work remove all but the /usr/bin/ copy of this config and restore that one.
Mtop
This is a monitoring tool much like Htop or top, but for mysql.
mysql -e "CREATE USER 'mysqltop'@'localhost';grant super, reload, process on *.* to 'mysqltop'@'localhost';" cd /usr/local/src wget http://downloads.sourceforge.net/project/mtop/mtop/v0.6.6/mtop-0.6.6.tar.gz tar -zxvf mtop-0.6.6.tar.gz rm -rf mtop-0.6.6.tar.gz cd mtop-0.6.6 perl Makefile.PL make make install mtop
ENJOY!!!
INNODB foo
Getting the number of InnoDB/MYISAM tables for one database
mysql -e "use databasename;show table status\G" | egrep 'Engine' | sort | uniq -c | sort -rn
Getting a list of all InnoDB/MYISAM tables on the server
InnoDB
mysql -e "SELECT concat(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.tables WHERE engine = 'InnoDB';" | tail -n +2 > /root/innodb.txt
MYISAM
mysql -e "SELECT concat(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.tables WHERE engine = 'MYISAM';" | tail -n +2 > /root/myisam.txt
Converting all table in the list to InnoDB or MYISAM
To convert make the list above.
Run the following to convert to MYISAM.
for I in $(cat /root/innodb.txt); do echo "ALTER TABLE $I ENGINE = MYISAM" | mysql; done
or run the following to convert to InnoDB.
for I in $(cat /root/myisam.txt); do echo "ALTER TABLE $I ENGINE = InnoDB" | mysql; done
1) Create a datadir for new mysql in /home cause its the largest drive:
mkdir /home/*datadir* chown mysql. /home/*datadir*
2) Copy all files keeping permission from /backup/*backup date*/var/lib/mysql to /home/*datadir*
cp -Rp /backup/*backup date*/var/lib/mysql/* /home/*datadir*/
3) Run the following to start a new instance of mysql:
mysqld_safe --socket=/home/*datadir*/mysql.sock --basedir=/ --datadir=/home/*datadir*/ --pid-file=/home/*datadir*/test.pid --skip-external-locking --skip-networking
4) Dump innodb database(s):
mysqldump --socket=/home/*datadir*/mysql.sock database_name > database_file.sql
5) Once complete stop mysql processes ((ps aux | grep mysql) then kill root and mysql process's that were created) and remove the /home/*datadir* directory.
mysql definer problems
Are you getting this error?
[root@host ~]# mysqldump LRN > /home/temp/test1.sql
mysqldump: Got error: 1449: "The user specified as a definer ('LRN_DB'@'%') does not exist" when using LOCK TABLES
You need to read the views with a new user to manage them.
How to fix
get in mysql:
mysql
Use the database in question:
Use db;
Show the create statement for the view:
SHOW CREATE VIEW $VIEW-NAME\G
This should give you an output like this:
MariaDB [LRN]> SHOW CREATE VIEW VIEW_Artists_Reviews\G
*************************** 1. row ***************************
View: VIEW_Artists_Reviews
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`LRN_DB`@`%` SQL SECURITY DEFINER VIEW `VIEW_Artists_Reviews` AS select `E`.`eventID` AS `eventID`,`E`.`eventType` AS
`eventType`,`E`.`eventTime` AS `eventTime`,`E`.`eventDate` AS `eventDate`,`E`.`eventCity` AS `eventCity`,`E`.`eventPlayPhoto` AS `eventPlayPhoto`,`E`.`playerA` AS
`playerA`,`E`.`playerAID` AS `playerAID`,`E`.`playerATable` AS `playerATable`,`E`.`hostValidated` AS `hostValidated`,`E`.`hostID` AS `hostID`,`H`.`showName` AS
`showName`,`H`.`primContactFirst` AS `primContactFirst`,`H`.`primContactLast` AS `primContactLast`,`H`.`adminStatus` AS `hostStatus`,`A`.`account_type` AS
`artistStatus`,`R`.`artistID` AS `artistID`,`R`.`artistName` AS `artistName`,`R`.`text4Recomm` AS `text4Recomm`,`R`.`text4Admin` AS `text4Admin`,`R`.`postingTime` AS
`postingTime`,`R`.`status` AS `status` from (((`Calendar` `E` join `Artists_Reviews` `R` on((`E`.`eventID` = `R`.`eventID`))) left join `Hosts` `H` on((`E`.`hostID` =
`H`.`huid`))) left join `Artists` `A` on((`R`.`artistID` = `A`.`uid`)))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set, 1 warning (0.00 sec)
Now you need to run an alter statement to correct the definer, which will start like this:
ALTER DEFINER = 'new_user'@'%' VIEW (place the output from the show create view from above here after the view name in the output.);
That should fix it.