TempDB Movement to different drive

We might land up with a requirement due to space constraints, where it is required to move TempDB files to a bigger drive. To achieve the same, follow the below listed steps where I will be moving the files from C:\ drive to D:\ drive ->

USE TempDB
GO
EXEC sp_helpfile
GO

Results will be something like:
name fileid filename filegroup size
——- —— ————————————————————– ———- ——-
tempdev 1 C:Program FilesMicrosoft SQL ServerMSSQLdatatempdb.mdf PRIMARY 16000 KB
templog 2 C:Program FilesMicrosoft SQL ServerMSSQLdatatemplog.ldf NULL 1024 KB

Along with other information related to the database. The names of the files are usually tempdev and templog by default. These names will be used in next statement.

Run following code, to move mdf and ldf files.

USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = ‘d:datatempdb.mdf’)
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = ‘e:datatemplog.ldf’)
GO

Note- Restart SQL Server services to bring this change in effect and you are done with this exercise.

Happy learning.

Cheers!
Aman Kharbanda

Advertisements

Backup and Restoration in MySQL

1. Backup a single database:

This example takes a backup of test database and dumps the output to tes.sql

# mysqldump -u root -p rootpassword test > test.sql
# mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

2. Backup multiple databases:

# mysqldump -u root -ptmppassword –databases test1 test2 > test1_test2.sql

Verify the test1_test2.sql dumpfile contains both the database backup.

# grep -i “Current database:” /tmp/test1_test2.sql
— Current Database: ‘mysql’
— Current Database: ‘test1’

3. Backup all the databases:

The following example takes a backup of all the database of the MySQL instance.

# mysqldump -u root -ptmppassword –all-databases > /tmp/all-database.sql

4. Backup a specific table:

In this example, we backup only the accounts table from test database.

# mysqldump -u root -ptmppassword test accounts \ > /tmp/test_accounts.sql


How To Restore MySQL database

1. Restore a database

In this example, to restore the sugarcrm database, execute mysql with < as shown below. When you are restoring the dumpfilename.sql on a remote database, make sure to create the sugarcrm database before you can perform the restore.

# mysql -u root -ptmppassword

mysql> create database sugarcrm;
Query OK, 1 row affected (0.02 sec)

# mysql -u root -ptmppassword sugarcrm < /tmp/sugarcrm.sql

# mysql -u root -p[root_password] [database_name] < dumpfilename.sql

2. Backup a local database and restore to remote server using single command:

This is a sleek option, if you want to keep a read-only database on the remote-server, which is a copy of the master database on local-server. The example below will backup the sugarcrm database on the local-server and restore it as sugarcrm1 database on the remote-server. Please note that you should first create the sugarcrm1 database on the remote-server before executing the following command.

[local-server]# mysqldump -u root -ptmppassword sugarcrm | mysql \
-u root -ptmppassword –host=remote-server -C sugarcrm1
[Note: There are two — (hyphen) in front of host]

Privileges, User Lists, Password Change Script for MySQL

Show privileges In MySQL :-

mysql> SHOW GRANTS FOR ‘root’@’localhost’;
+———————————————————————+
| Grants for root@localhost |
+———————————————————————+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ WITH GRANT OPTION |
+———————————————————————+

To list the privileges granted to the account that you are using to connect to the server, you can use any of the following statements:

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

To get more knowledge and details on privileges->

mysql> SHOW PRIVILEGES\G

List of MYSQL users and their privileges –>

mysql> select user,host from mysql.user;
+——————+————–+
| user | host |
+——————+————–+
| root | % |
| root | 127.0.0.1 |
| debian-sys-maint | localhost |
| root | localhost |
| root | stack.domain |
+——————+————–+
8 rows in set (0.01 sec)
The above output shows a list of existing MySQL accounts. Note that a MySQL account has two components: user and host. This allows the same user to use different MySQL accounts depending on which host they are connecting from. “%” is a wildcard character interpreted as “any” host.

To find the privilege(s) granted to a particular MySQL account:

mysql> show grants for ‘root’@’%’;
+————————————————+
| Grants for root@% |
+————————————————+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ …. |
+————————————————+
1 row in set (0.00 sec)

To change the password of a given account:

SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘Test@1234’);

Create User in MySQL:
CREATE USER ‘test_user’@’localhost’ IDENTIFIED BY ‘XXXX’;

Grant Privileges to user:
grant select on *.* to ‘new_user’@’%’ identified by ‘xxxxx’ with grant option;

After granting privileges, use the below command to bring that in effect-
mysql> flush privileges;

DB Size and Free Space Availability Script for MySQL

Script to check MySQL DB size in MB’s->

SELECT table_schema “Data Base Name”, sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB”
FROM information_schema.TABLES GROUP BY table_schema ;

Script to view the Free space available for my DB in MySQL ->

SELECT table_schema “Data Base Name”,
sum( data_length + index_length ) / 1024 /
1024 “Data Base Size in MB”,
sum( data_free )/ 1024 / 1024 “Free Space in MB”
FROM information_schema.TABLES
GROUP BY table_schema ;

MySQL Installation on Unix Platform

Installation ->

Download 3 packages from MySQL Download Website:
MySQL-client-5.6.10-1.e16.x86_64.rpm
MySQL-server-5.6.10-1.e16.x86_64.rpm
MySQL-shared-5.6.10-1.e16.x86_64.rpm

Lists the downloaded file in respective directory:
ls – ltr

Check size of these packages —
du -sh * (Gives the size of package)

Installation Process –
Step 1) RPM the packages one by one ::
rpm -ivh MySQL-client-5.6.10-1.e16.x86_64.rpm

(Here ivh means :: i- Installation v- Verbose h-Hash)

–RPM all the packages -> Client/Server/Shared.

–After installation, a random pwd will be set for the root user. We can find that pwd in ‘/root/.mysql_secret’.

–Find that secret pwd ->
cat /root/.mysql_secret

–After Installation, we need to start the mysql service –>
service mysql start

chkconfig mysql on (This is to enable the services in Automatic mode from now on).

–Check the mysql port by:
nmap localhost
(by default its 3306)

— Now open mysql in CLI mode–>
mysql -h localhost -Uroot -Ppwd

–After logging in, you have to reset the pwd for root user (From the secret one)–>
set password for root@’localhost’=password(‘password@123’);
flush privileges

— Create a new DB–>
create database testdb;

–List all DB’s–>
show databases;

Upgrading MySQL server on Windows platform

Upgrade MySQL RDBMS from Version 5.5.xx to 5.5.38 on Windows Platform ->

Backup current MySQL Installation.
c:\Program Files\MySQL\MySQL Server 5.5\bin>mysqldump    -uroot   -p  –routines –triggers  –events –all-databases  > backup_location\backup_filename

Please verify the backup file with PowerShell command for dump completed successfully

PS C:\Users\user1> get-content ” backup_location\backup_filename ” | select   -last  5
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

— Dump completed on YYYY-MM-DD HH:MM:SS

Take a copy  c:\Program Files\MySQL\MySQL Server 5.1\my.ini   to backup_location

Stop the current MySQL instance

Stopping the MySQL service from windows services.

Or execute the below listed command –
c:\Program Files\MySQL\MySQL Server 5.5\bin>mysqladmin.exe   -uroot   -p   shutdown

You can download the installer from the following link and open the wizard to proceed with the upgrade process->
Download Link

1

Click Next

2

Click Next

3

Select the option which you have done in previous version installation. If you are not sure then select complete.

4

Click Next
5
6
7

8

Uncheck the Launch the MySQL Instance Configuration Wizard and click Finish.

Start MySQL instance
Starting the MySQL service from windows services.

Please execute mysql_upgrade from the command line

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql_upgrade -uroot –p
Enter password: ******
Looking for ‘mysql.exe’ as: C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe
Looking for ‘mysqlcheck.exe’ as: C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqlcheck.exe
Running ‘mysqlcheck’ with connection arguments: “–port=3306”
Running ‘mysqlcheck’ with connection arguments: “–port=3306”
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.slow_log OK
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
sample1.s1 OK
Running ‘mysql_fix_privilege_tables’…
OK

MySQL upgrade completes with these steps…

Login to the server and check everything is fine.