Setting, Changing And Resetting MySQL Root Passwords

Mysqladmin Command To Change Root Password

Method 1 - Set up root password for the first time

If you have never set a root password for MySQL, the server does not require a password at all for connecting as root. To set up a root password for the first time, use the mysqladmin command at the shell prompt as follows:

$ mysqladmin -u root password newpass

If you want to change (or update) a root password, then you need to use the following command:

$ mysqladmin -u root -p oldpassword newpass

Enter password:

If you get...

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

then follow the instructions below on how to recover your MySQL password.

Change MySQL password for other users

To change a normal user password you need to type:

$ mysqladmin -u user-name -p oldpassword newpass

 

Method 2 - Update or change password

MySQL stores usernames and passwords in the user table inside the MySQL database. You can directly update a password using the following method to update or change passwords:
1) Login to the MySQL server, type the following command at the shell prompt:

$ mysql -u root -p

2) Use the mysql database (type commands at the mysql> prompt):

mysql> use mysql;

3) Change password for a user:

mysql> update user set password=PASSWORD("newpass") where User='ENTER-USER-NAME-HERE';

4) Reload privileges:

mysql> flush privileges;
mysql> quit

This method you need to use while using PHP or Perl scripting.

Recover MySQL root password

You can recover a MySQL database server password with the following five easy steps:
Step # 1: Stop the MySQL server process.
Step # 2: Start the MySQL (mysqld) server/daemon process with the --skip-grant-tables option so that it will not prompt for a password.

Step # 3: Connect to the MySQL server as the root user.
Step # 4: Set a new root password.
Step # 5: Exit and restart the MySQL server.
Here are the commands you need to type for each step (log in as the root user):
Step # 1 : Stop the MySQL service:

# /etc/init.d/mysql stop

Output:

Stopping MySQL database server: mysqld.

Step # 2: Start the MySQL server w/o password:

# mysqld_safe --skip-grant-tables &

Output:

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step # 3: Connect to the MySQL server using the MySQL client:

# mysql -u root

Output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Step # 4: Set a new MySQL root user password:

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Step # 5: Stop the MySQL server:

# /etc/init.d/mysql stop

Output:

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended

[1]+  Done                    mysqld_safe --skip-grant-tables

Start the MySQL server and test it:

# /etc/init.d/mysql start
# mysql -u root -p

sources : https://www.howtoforge.com/setting-changing-resetting-mysql-root-passwords

Read More

Cara Cek Mysql Replikasi Berjalan atau Tidak dan Cara mengatasinya jika eror

Masuk mysql ke server replikasi

replikasi@localhost~#mysql –u root –p             

ketik perintah                 

mysql>show slave status \G;

perhatikan gambar dibawah jika replikasi berjalan dengan baik maka statusnya untuk slave io dan sql :yes

Cara mengatasi jika eror

Masuk server monitoring replikasi kemudian jalan kan script dibawah ini dimana sudah disimpan di server ini dan mohon seseuaikan dan perhatikan antara server master dan slave

#!/bin/bash

RSYNC=/usr/bin/rsync

SSH=/usr/bin/ssh

SHELL=/bin/sh

PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

#### (PERHATIKAN SCRIPT SECARA DETAIL KE BAWAH, UBAH SESUAI KEBUTUHAN)

### VARIABEL DATABASE

## silahkan di perbanyak jika database lebih dari satu dengan di pisahkan spasi, seperti contoh array=( cc_diknas takaful_cc pfizer )

array=( purence_telesales )

### VARIABEL MASTER

ip_master=192.168.0.25

user_master=root

pass_master=J@R1ng

dbuser_master=root

dbpass_master=J@R1ng

folder_database_master=/var/lib/mysql/

folder_log_master=/var/lib/mysql/

### VARIABEL SLAVE

ip_slave=192.168.0.141

user_slave=root

pass_slave=J@R1ng

dbuser_slave=root

dbpass_slave=

folder_database_slave=/var/lib/mysql/

folder_log_slave=/var/lib/mysql/

### VARIABEL MONITOR REPLIKASI

# ganti nama file notif

file_monitor=/home/script_monitoring/notif_purence.sh

port=3306

backupdir=/home/temp_database/

### DUMP DATABASE AND RESTORE DATABASE

## DUMP DATABASEE and extract

for database1 in "${array[@]}"

do               

    rm -r -f $backupdir/$database1.sql

    # dengan password mysql master

    mysqldump --opt -u $dbuser_master -p$dbpass_master --host=$ip_master --port=$port --databases $database1 | gzip >  $backupdir/$database1.sql.gz

       

    # tanpa password mysql master

    # mysqldump --opt -u $dbuser_master --host=$ip_master --port=$port --databases $database1 | gzip >  $backupdir/$database1.sql.gz

    sleep 5s

    echo selesai backup database master       

    gunzip -S .zip $backupdir/$database1.sql.gz

    sleep 5s

    echo selesai extract database master

## RESTORE DATABASE and delete

    # dengan password mysql slave

    # mysql -u $dbuser_slave -p $dbpass_slave -h $ip_slave -e "show databases"

    # tanpa password mysql slave

    mysql -u $dbuser_slave -h $ip_slave -e "drop database $database1"

    sleep 5s

    echo selesai hapus database slave

    mysql -u $dbuser_slave -h $ip_slave -e "source $backupdir/$database1.sql"

    sleep 5s

    echo selesai import database slave

done

### SLAVE PROSES

sshpass -p $pass_slave ssh -o StrictHostKeyChecking=no $user_slave@$ip_slave "/etc/init.d/mysqld stop"

echo selesai stop mysqld slave

sleep 5s

sshpass -p $pass_slave ssh -o StrictHostKeyChecking=no $user_slave@$ip_slave "find  $folder_database_slave  -name 'master.info' -exec rm -f {} \;"

sshpass -p $pass_slave ssh -o StrictHostKeyChecking=no $user_slave@$ip_slave "find  $folder_database_slave  -name 'binlog.*' -exec rm -f {} \;"

sshpass -p $pass_slave ssh -o StrictHostKeyChecking=no $user_slave@$ip_slave "find  $folder_database_slave  -name '*bin.*' -exec rm -f {} \;"

sshpass -p $pass_slave ssh -o StrictHostKeyChecking=no $user_slave@$ip_slave "find  $folder_database_slave  -name '*.index' -exec rm -f {} \;"

sshpass -p $pass_slave ssh -o StrictHostKeyChecking=no $user_slave@$ip_slave "find  $folder_database_slave  -name '*.err' -exec rm -f {} \;"

sshpass -p $pass_slave ssh -o StrictHostKeyChecking=no $user_slave@$ip_slave "find  $folder_database_slave  -name '*.pid' -exec rm -f {} \;"

sshpass -p $pass_slave ssh -o StrictHostKeyChecking=no $user_slave@$ip_slave "find  $folder_database_slave  -name '*-log*' -exec rm -f {} \;"

echo selesai hapus log slave

sleep 5s

### MASTER PROSES

sshpass -p $pass_master ssh -o StrictHostKeyChecking=no $user_master@$ip_master "/etc/init.d/mysqld stop"

echo selesai stop mysqld master

sleep 5s

sshpass -p $pass_master ssh -o StrictHostKeyChecking=no $user_master@$ip_master "find  $folder_log_master  -name '*bin.*' -exec rm -f {} \;"

sshpass -p $pass_master ssh -o StrictHostKeyChecking=no $user_master@$ip_master "find  $folder_log_master  -name '*bin.*' -exec rm -f {} \;"

sshpass -p $pass_master ssh -o StrictHostKeyChecking=no $user_master@$ip_master "find  $folder_log_master  -name '*.err' -exec rm -f {} \;"

sshpass -p $pass_master ssh -o StrictHostKeyChecking=no $user_master@$ip_master "find  $folder_log_master  -name '*.pid' -exec rm -f {} \;"

sshpass -p $pass_master ssh -o StrictHostKeyChecking=no $user_master@$ip_master "find  $folder_log_master  -name 'binlog.*' -exec rm -f {} \;"

echo selesai hapus log master

sleep 5s

sshpass -p $pass_master ssh -o StrictHostKeyChecking=no $user_master@$ip_master "/etc/init.d/mysqld start"

echo selesai start mysqld master

sleep 5s

### SLAVE PROSES

sshpass -p $pass_slave ssh -o StrictHostKeyChecking=no $user_slave@$ip_slave "/etc/init.d/mysqld start"

echo selesai start mysqld slave

sleep 5s

### MONITOR REPLIKASI PROSESS

$file_monitor

echo SELESAI PROSESS REPLIKASI SILAHKAN CEK EMAIL ANDA

++++++++++++++++++++++++++

Simpan script diatas dimana penulis menyimpan di lokasi /home/script_replikasi/purence dengan nama purence_repl_mysql_mycnf_dump.sh

Masuk direktori tersebut kemudian eksekusi file tersebut

Proses saat menjalankan script diatas

masih eror

Read More