Archive

Posts Tagged ‘mysql’

Changing MySQL data directory CentOS 7

January 14, 2016 Leave a comment

Doing this on CentOS 7 64 bit and MySQL 5.6 community edition

Sometimes it is better to put mysql in a separate partition than its regular location

Typically the mysql database are located in /var/lib/mysql

I want to change it to /var/data/mysql 

Modify the paths as required in the below commands

stop mysql

systemctl stop mysqld.service

create new mysql data directory

mkdir /var/data/mysql

modify /etc/my.cnf and point to new data directory – add the client section to the top

[client]
port=3306
socket=/var/data/mysql/mysql.sock

[mysqld]
datadir=/var/data/mysql
socket=/var/data/mysql/mysql.sock

copy all files from /var/lib/mysql to the new directory /var/data/mysql

cp -r /var/lib/mysql/* /var/data/mysql

permissions for the new directory

chown -R mysql /var/data/mysql;
chgrp -R mysql /var/data/mysql;
chmod -R g+rw /var/data/mysql;

also modify SELINUX settings to allow mysql to use the different path

# add context and make it permanent 
semanage fcontext -a -s system_u -t mysqld_db_t "/var/data/mysql(/.*)?"
restorecon -Rv /var/data/mysql

start mysql

systemctl start mysqld.service

 

MySQL should start cleanly.
You can verify the change by creating a test database.
Then go to /var/data/mysql and you should be able to see the new database there

Categories: MySQL Tags: ,

MySQL command prompt auto-complete

September 25, 2014 1 comment

If you work on the mysql command line, its nice to have an autocomplete similar to the linux bash where you press the TAB key and the commands complete.

In mysql, we can set it up to a degree to provide some hints when we type. Though it might not complete everything you type, it does complete table names etc.

2 ways to do it ( I am on CentOS 6.5 )

when you log into mysql, use the auto-rehash config option like

mysql --auto-rehash -u root -p

if this does not work, then try creating a file called .my.cnf in your home directory and put the below into it

[mysql]
auto-rehash

Clear Screen for MySQL Command Prompt – Linux

On Linux, if you have logged into mysql on the command prompt  and want to clear all text on screen you can use

mysql> \! clear

\! system-command is the command format

\! tells mysql to pass the command to the system

Example

[naveen@localhost ~]$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.73-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| drupal |
| employees |
| mysql |
| phpmyadmin |
| test |
| zabbix |
+--------------------+
7 rows in set (0.00 sec)
mysql> \! clear
Categories: MySQL, Tips and Tricks Tags: ,