mysql

main.md

Reset password

sudo /etc/init.d/mysql stop
sudo mysqld --skip-grant-tables &
mysql -u root mysql
UPDATE mysql.user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root';
FLUSH PRIVILEGES;
exit;   
sudo /etc/init.d/mysql start 

Slow log

log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time         = 1

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

Get users

select host, user, password from mysql.user;
+--------------+------------------+-------------------------------------------+
| host         | user             | password                                  |
+--------------+------------------+-------------------------------------------+
| localhost    | root             |                                           |
| 127.0.0.1    | root             | *1A4CAF139A847B5ABAC1A964946A3D1D25B3563A |
| ::1          | root             | *1A4CAF139A847B5ABAC1A567946A3D1D25B31C3A |

Rename user

 RENAME USER 'user'@'172.16.1.1' to 'user'@'172.17.1.%';

Createuser

CREATE USER 'user'@'%' IDENTIFIED BY 'password';

Grant user

grant select on databasename.* to 'user'@'127.0.0.1';

Where is tmpdir

SHOW VARIABLES LIKE 'tmpdir';

Show grants for user

SHOW GRANTS FOR CURRENT_USER();

Show queries

show full processlist \G;

mysql_create_slave.md

How to setup a slave for replication in 6 simple steps with Percona XtraBackup

STEP 1: Make a backup on TheMaster and prepare it¶

At TheMaster, issue the following to a shell:

TheMaster$ xtrabackup --backup --user=yourDBuser --password=MaGiCdB1 --target-dir=/path/to/backupdir

After this is finished you should get:

xtrabackup: completed OK!

This will make a copy of your MySQL data dir to the /path/to/backupdir directory. You have told Percona XtraBackup to connect to the database server using your database user and password, and do a hot backup of all your data in it (all MyISAM, InnoDB tables and indexes in them).

In order for snapshot to be consistent you need to prepare the data:

TheMaster$ xtrabackup --user=yourDBuser --password=MaGiCdB1
--prepare --target-dir=/path/to/backupdir

You need to select path where your snapshot has been taken. If everything is ok you should get the same OK message. Now the transaction logs are applied to the data files, and new ones are created: your data files are ready to be used by the MySQL server.

Percona XtraBackup knows where your data is by reading your my.cnf. If you have your configuration file in a non-standard place, you should use the flag --defaults-file =/location/of/my.cnf.

If you want to skip writing the user name and password every time you want to access MySQL, you can set it up in .mylogin.cnf as follows:

mysql_config_editor set --login-path=client --host=localhost --user=root --password

For more information, see MySQL Configuration Utility <https://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html.

This is will give you root access to MySQL.

STEP 2: Copy backed up data to TheSlave¶

Use rsync or scp to copy the data from Master to Slave. If you’re syncing the data directly to slave’s data directory it’s advised to stop the mysqld there.

TheMaster$ rsync -avpP -e ssh /path/to/backupdir TheSlave:/path/to/mysql/

After data has been copied you can back up the original or previously installed MySQL datadir (NOTE: Make sure mysqld is shut down before you move the contents of its datadir, or move the snapshot into its datadir.):

TheSlave$ mv /path/to/mysql/datadir /path/to/mysql/datadir_bak

and move the snapshot from TheMaster in its place:

TheSlave$ xtrabackup --move-back --target-dir=/path/to/mysql/backupdir

After you copy data over, make sure MySQL has proper permissions to access them.

TheSlave$ chown mysql:mysql /path/to/mysql/datadir

In case the ibdata and iblog files are located in different directories outside of the datadir, you will have to put them in their proper place after the logs have been applied.

STEP 3: Configure The Master’s MySQL server¶

Add the appropriate grant in order for slave to be able to connect to master:

TheMaster|mysql GRANT REPLICATION SLAVE ON . TO 'repl'@'$slaveip' IDENTIFIED BY '$slavepass';

Also make sure that firewall rules are correct and that TheSlave can connect to TheMaster. Test that you can run the mysql client on TheSlave, connect to TheMaster, and authenticate.

TheSlave$ mysql --host=TheMaster --user=repl --password=$slavepass

Verify the privileges.

mysql SHOW GRANTS;

STEP 4: Configure The Slave’s MySQL server¶

First copy the my.cnf file from TheMaster to TheSlave:

TheSlave$ scp user@TheMaster:/etc/mysql/my.cnf /etc/mysql/my.cnf

then change the following options in /etc/mysql/my.cnf:

server-id=2

and start/restart mysqld on TheSlave.

In case you’re using init script on Debian based system to start mysqld, be sure that the password for debian-sys-maint user has been updated and it’s the same as that user’s password on the TheMaster. Password can be seen and updated in /etc/mysql/debian.cnf.

STEP 5: Start the replication¶

Look at the content of the file xtrabackup_binlog_info, it will be something like:

TheSlave$ cat /var/lib/mysql/xtrabackup_binlog_info TheMaster-bin.000001 481

Execute the CHANGE MASTER statement on a MySQL console and use the username and password you’ve set up in STEP 3:

TheSlave|mysql CHANGE MASTER TO MASTER_HOST='$masterip', MASTER_USER='repl', MASTER_PASSWORD='$slavepass', MASTER_LOG_FILE='TheMaster-bin.000001', MASTER_LOG_POS=481;

and start the slave:

TheSlave|mysql START SLAVE;

STEP 6: Check¶

You should check that everything went OK with:

TheSlave|mysql SHOW SLAVE STATUS \G ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ... Seconds_Behind_Master: 13 ...

Both IO and SQL threads need to be running. The Seconds_Behind_Master means the SQL currently being executed has a current_timestamp of 13 seconds ago. It is an estimation of the lag between TheMaster and TheSlave. Note that at the beginning, a high value could be shown because TheSlave has to “catch up” with TheMaster.

Adding more slaves to The Master¶

You can use this procedure with slight variation to add new slaves to a master. We will use Percona XtraBackup to clone an already configured slave. We will continue using the previous scenario for convenience but we will add TheNewSlave to the plot.

At TheSlave, do a full backup:

TheSlave$ xtrabackup --user=yourDBuser --password=MaGiCiGaM
--backup --slave-info --target-dir=/path/to/backupdir

By using the --slave-info Percona XtraBackup creates additional file called xtrabackup_slave_info.

Apply the logs:

TheSlave$ xtrabackup --prepare --use-memory=2G --target-dir=/path/to/backupdir/

Copy the directory from the TheSlave to TheNewSlave (NOTE: Make sure mysqld is shut down on TheNewSlave before you copy the contents the snapshot into its datadir.):

rsync -avprP -e ssh /path/to/backupdir TheNewSlave:/path/to/mysql/datadir

Add additional grant on the master:

TheMaster|mysql GRANT REPLICATION SLAVE ON . TO 'repl'@'$newslaveip' IDENTIFIED BY '$slavepass';

Copy the configuration file from TheSlave:

TheNEWSlave$ scp user@TheSlave:/etc/mysql/my.cnf /etc/mysql/my.cnf

Make sure you change the server-id variable in /etc/mysql/my.cnf to 3 and disable the replication on start:

skip-slave-start server-id=3

After setting server_id, start mysqld.

Fetch the master_log_file and master_log_pos from the file xtrabackup_slave_info, execute the statement for setting up the master and the log file for The NEW Slave:

TheNEWSlave|mysql CHANGE MASTER TO MASTER_HOST='$masterip', MASTER_USER='repl', MASTER_PASSWORD='$slavepass', MASTER_LOG_FILE='TheMaster-bin.000001', MASTER_LOG_POS=481;

and start the slave:

TheNEWSlave|mysql START SLAVE;

If both IO and SQL threads are running when you check the TheNewSlave, server is replicating TheMaster.