Category Archives: SQL

How to reset MySQL root password

Linux, SQL, , ,

Step 1: Stop MySQL Service.

sudo stop mysql

Step 2: Kill all running mysqld.

sudo killall -9 mysqld

Step 3: Starting mysqld in Safe mode.

sudo mysqld_safe --skip-grant-tables --skip-networking &

Step 4: Start mysql client

mysql -u root

Step 5: After successful login, please execute this command to change any password.

FLUSH PRIVILEGES;

Step 6: You can update mysql root password .

UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';

Step 7: Please execute this command.

FLUSH PRIVILEGES;

Gently copied from http://askubuntu.com/questions/489098/unable-to-reset-root-password-of-mysql

Original solution from http://www.dbrnd.com/2015/08/reset-mysql-root-password-ubuntu/

mysqldump > unknown table engine ‘PERFORMANCE_SCHEMA’

Linux, SQL

Lets simply force mysqldump to ignore all performance_schema tables.

# mysqldump -u <username> -p <password> --all-databases > /root/mysql.full.dump --ignore-table=performance_schema.cond_instances --ignore-table=performance_schema.events_waits_current --ignore-table=performance_schema.cond_instances --ignore-table=performance_schema.events_waits_history --ignore-table=performance_schema.events_waits_history_long  --ignore-table=performance_schema.events_waits_summary_by_instance --ignore-table=performance_schema.events_waits_summary_by_thread_by_event_name --ignore-table=performance_schema.events_waits_summary_global_by_event_name --ignore-table=performance_schema.file_instances --ignore-table=performance_schema.file_summary_by_event_name  --ignore-table=performance_schema.file_summary_by_instance  --ignore-table=performance_schema.mutex_instances --ignore-table=performance_schema.performance_timers  --ignore-table=performance_schema.rwlock_instances --ignore-table=performance_schema.setup_consumers --ignore-table=performance_schema.setup_instruments --ignore-table=performance_schema.setup_timers --ignore-table=performance_schema.threads

But remember!
An error is always an error!
This solution isn’t 100% recommended!

SQLSTATE[HY000] [2000] mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication

Laravel, PHP, SQL

 

SQLSTATE[HY000] [2000] mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD(‘your_existing_password’). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file

This is a small script, made by Antonio Bonifati and found at StackOverflow Cannot connect to MySQL 4.1+ using old authentication  that will update your password and it will solve this problem for you.

<?php

$hostname = '127.0.0.1';
$database = 'database_name';
$username = 'database_username';
$password = 'password';

if (!mysql_connect($hostname, $username, $password)) {
 die(mysql_error());
} if (!mysql_query($query = 'SET SESSION old_passwords=FALSE')) {
 die($query);
} if (!mysql_query($query = "SET PASSWORD = PASSWORD('" .$password . "')")) {
 die($query);
}
echo "Excellent, mysqli will now work";

exit();
?>

MySQL vs/and/plus/more JSON

Big Data, SQL,

Here are a few things that we can use / read about MySQL and JSON.

Small benchmark tests of reading 20000

Krasimir Tsonev has made a small benchmark tests of reading 20000 from a MySQL db and from a JSON file.

I’m gonna just put the *end* results. You can read all the article on Krasimir’s blog about MySQL vs JSON file data storing benchmark results.

MySQL

ab -n 30 -c 30 http://localhost/mqsql.php
Concurrency Level:      30
Time taken for tests:   30.518 seconds

 

JSON

ab -n 30 -c 30 http://localhost/json.php
Concurrency Level:      30
Time taken for tests:   3.384 seconds
MySQL query to JSON
username email
mike [email protected]
jane [email protected]
stan [email protected]

The MySQL query

SELECT 
     CONCAT("[",
          GROUP_CONCAT(
               CONCAT("{username:'",username,"'"),
               CONCAT(",email:'",email),"'}")
          )
     ,"]") 
AS json FROM users;

The JSON result

[
     {username:'mike',email:'[email protected]'},
     {username:'jane',email:'[email protected]'},
     {username:'stan',email:'[email protected]'}
]

Got this from http://www.thomasfrank.se/mysql_to_json.html.