By:
On:
In: Database, General
With: 0 Comments

You might receive the error Column count of mysql.user is wrong. Expected 42, found 44. The table is probably corrupted , this is probably because you import the whole database from mysql 5.6 to 5.7 . The table structure has changed , so you need to update it. You can use the command mysql_upgrade to do this work for you. After you import the data , run this file mysql_upgrade -uroot -p -h127.0.0.1 -P3306   this command will go all over your databases table and upgrade the database structure . mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK UpgradingRead More →

By:
On:
In: Database, Linux
With: 0 Comments

This is a bit gnarly. If you have a better method of updating the password without triggering a warning about PASSWORD being deprecated, I’m all ears. # Stop MySQL sudo service mysql stop # Make MySQL service directory. sudo mkdir /var/run/mysqld # Give MySQL user permission to write to the service directory. sudo chown mysql: /var/run/mysqld # Start MySQL manually, without permission checks or networking. sudo mysqld_safe –skip-grant-tables –skip-networking & # Log in without a password. mysql -uroot mysql Update the password for the root user. UPDATE mysql.user SET authentication_string=PASSWORD(‘YOURNEWPASSWORD’), plugin=’mysql_native_password’ WHERE User=’root’ AND Host=’%’; EXIT; # Turn off MySQL. sudo mysqladmin -S /var/run/mysqld/mysqld.sock shutdownRead More →

By:
On:
In: Database
With: 0 Comments

Below are the steps to fix this issue: 1. Shut down your old slave server and make a copy of the data directory to your new slave. Make sure you get the old slave’s master.info and relay-log.info files. Once you have a copy of the data directory, you can restart the old slave if desired. The rest of these steps are performed on the new slave. 2. Rename the master.info to master.info.oldslave and relay-log.info to relay-log.info.oldslave. 3. Edit /etc/my.cnf and add the line “skip-slave-start” (without the quotes) to the [mysqld] section. 4. Start the new slave. This will create new master.info and relay-log.info files. BecauseRead More →

By:
On:
In: Database
With: 0 Comments

SQL Thread is not running When relay log is corrupted  , meaning the slave server is stopped unexpectedly How to fix: – run show slave , we’ll see the IO thread running but SQL thread is not working. – check Relay_Log_File name , for example , mysqld_3308-relay-bin.000044 then we know that 44 is corrupted, so we’ll increase it stop slave; change MASTER To Relay_Log_File=’mysqld_3308-relay-bin.000045′,Relay_Log_Pos=4; start slave; SQL IO is not runnning run show slave to check Master_Log_File  value , then increase it to 1 Check  : Master_Log_File mysql-bin.003591 stop slave; change MASTER To Master_Log_File=’mysql-bin.003592′,MASTER_LOG_POS=4; start slave;  Read More →

By:
On:
In: Database
With: 0 Comments

Dump multiple databases: mysqldump -u root -p –databases db1 db2 db3 >dump.sql Dump some selected tables: mysqldump -u mysqlusername -p databasename table1 table2 table3 > databasebackup.sql Dome all tables except some tables: mysqldump -u username -p database –ignore-table=database.table1 –ignore-table=database.table2 > database.sql  Read More →

By:
On:
In: Database
With: 0 Comments

If you just uncomment the slow query log variable in mysqld.conf , you can’t start your mysql server. it’s because the variable name has changed, the following configuration works well for me. nano /etc/mysql/mysql.conf.d/mysqld.conf # Here you can see queries with especially long duration slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow-queries.log long_query_time = 1Read More →