Moving MySQL/Windows to same version of MySQL/Linux. You can mysqldump all the databases as follows:
C:\> mysqldump -uroot -p --routines --triggers --flush-privileges --all-databases > MySQLData.sql
Move MySQLData.sql to Linux box and run the reload
mysql -uroot -p < MySQLData.sql
While I was installing idoit- the cmdb and IT documenting platform, struggling with a pre-requisite of having mysql version 5.6 which is not shipped by native in ubuntu 14 installations. So had to google it and found some solution, however the one that worked for me which I’m sharing below:
step 1 : remove old mysql
sudo apt-get remove mysql-server sudo apt-get autoremove
step 2 : install new version of mysql
sudo apt-get install mysql-client-5.6 mysql-client-core-5.6 sudo apt-get install mysql-server-5.6
If you need to increase MySQL Connections without MySQL restart do like below
mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 100 | +-----------------+-------+ 1 row in set (0.00 sec) mysql> SET GLOBAL max_connections = 150; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 150 | +-----------------+-------+ 1 row in set (0.00 sec) These settings will change at MySQL Restart.
For permanent changes add below line in my.cnf and restart MySQL
max_connections = 150
For all you Ubuntu/MySQL developers out there, have you ever seen the following?
neo@thematrix:~$ sudo /etc/init.d/mysql restart * Stopping MySQL database server mysqld [fail] * Starting MySQL database server mysqld [ OK ] /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'
So, what is this “debian-sys-maint” user? Well, this MySQL user is created for the Ubuntu to be able to start/stop the database and to carry out other maintenance operations.
Sounds well enough, but then why do I keep running into the “access denied” problem for this user? Well, the issue is that with each update to MySQL, the user’s password in the database is overwritten. Ubuntu seems to go to the file /etc/mysql/debian.cnf in order to find this user’s password, but obviously the password is out of sync after the update has been applied.
As a result of this behaviour, I’ll run into the “access denied” problem every so often. Thankfully, the solution to this issue is fairly simple. Continue reading “Fixing error: ‘Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES)’” »
Use the following steps to reset a MySQL root password by using the command line interface.
Stop the MySQL service
(Ubuntu and Debian) Run the following command:
sudo /etc/init.d/mysql stop
(CentOS, Fedora, and Red Hat Enterprise Linux) Run the following command:
sudo /etc/init.d/mysqld stop
Start MySQL without a password
Run the following command. The ampersand (&) at the end of the command is required. Continue reading “Reset a MySQL root password for Debian” »
Some time we faces issues with MySQL installation on Linux machine. If we simply remove MySQL packages and re-install doesn’t fixes the issue, in that case old settings may still exists on server which again affects new install. In that case first uninstall MySQL completely from system and erase all settings of old install. To do the same follow the below settings.
Note: Please do not use below steps if MySQL have any running databases.
Step 1: Uninstall MySQL Packages
First uninstall all the MySQL packages installed on your server
# yum remove mysql mysql-server
Step 2: Romove MySQL Directory
Now we need to remove MySQL data directory from system which by default exists at/var/lib/mysql. If you didn’t find this, It may be changed to some other place, which you can find in my.cnf file with variable datadir. Delete the /var/lib/mysql directory from system but we prefer to rename it to keep a backup of existing files.
# mv /var/lib/mysql /var/lib/mysql_old_backup Continue reading “Reinstalling MySQL on CentOS/Redhat 6” »
$ rpm -Uhv http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
$ yum install -y pv
$ pv /home/user/my_big_dump.sql | mysql -uroot -p
apt-get install pv
sudo service mysql stop
I am using CentOS 6.4 box, so it requires me to install EPEL repo at the first place:
$ rpm -Uhv http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
Install pv using yum:
$ yum install -y pv
Installation done. Let the importing begin!
$ pv /home/user/my_big_dump.sql | mysql -uroot -p
For Ubuntu/Debian distribution intall PV using-
apt-get install pv
You can use the following script to dump mysql database and can add to crontab entry for daily backup!
#!/bin/sh
DAY=`/bin/date +%Y%m%d`
TFILE="/backup/MYDB.$DAY.gz"
mysqldump -u root -p'MYPASSWORD' MYDB | gzip > $TFILE
echo "cache dump completed, dump script by rumi (hasan.emdad@mango.com.bd)"
