Category: SQL
How To Migrate Database From Microsoft SQL Server 2000 To Microsoft SQL Server 2014
It is easy to Migrate Database From Microsoft SQL Server 2000 To Microsoft SQL Server 2014
Before you start make sure that you have administrative accounts on both servers.
This is 2 step process.
I would recommend reading this post for user export
Also read this document from Microsoft knowledge base.
1. we are going to export database using SQL Scripts from our old MsSQL 2000 server and then use that SQL Script file to create new version of Same database on our New MsSQL2014 server.
Ok on SQL 2000 server right click database you want to migrate then click on Generate SQL Script… as shown in figure 1
Then click on Show All button as shown in Figure 2
Now choose objects to create scripts for:
Next we decide on Formatting as shown in Figure 4
Finally we need to setup some Security and Table Scripting Options and as well one or multiple files, I prefer to use single file per database as shown in Figure 5.
Click Ok and wait for server to do its thing.
Now To the 2014 server…
Open Microsoft SQL Server Management Studio and connect to your 2014 server
click on open file or Ctrl + O (letter o not zero)
find your Exported SQL Script file from 2000 server.
Check your CREATE DATABASE directive and make sure that FILENAME is pointing to the place where you want new database to be stored.
2. Once all this is done we can do data Import using SQL Server Import and Export Wizard as explained in this post.
moving mysql databases using mysqldump & ssh
moving mysql databases using mysqldump & ssh
Moving MySQL Databases Using mysqldump
On old server:
Check the /etc/mysql/my.cnf file and make note of the address listed in bind-addresses.
On the new server:
When you install mysql, define a temporary password to root. This password will get overwritten during the transfer, after a restart of the mysql service.
Add a new interface eth0:x in /etc/network/interfaces with the ip address noted in the old server’s /etc/mysql/my.cnf file. LEAVE THIS INTERFACE DOWN UNTIL THE FINAL SWITCH.
Edit the /etc/mysql/my.cnf file
bind-addresses = <address of the new server> or, less specific, 0.0.0.0
Restart the service mysql. NOTE: reload doesn’t load the changes in my.cnf.
Service mysql restart
Use this command to move the databases:
ssh (your username)@(old-server’s FQDN or IP) “mysqldump -u (db-username, probably root) –all-databases > /(dirpath)/(filename)” | “mysql -u root -p (temp pw designated at mysql install on the new server) -h (ip address of new server) < /(dirpath)/(filename)”
NOTE: After the restoration of the databases on the new server, your current credentials will work until the mysql service is restarted.
If, for any reason, you need to do a complete re-install of MySQL, use this procedure to remove MySQL completely from server:
service mysql stop #or mysqld
deluser mysql
delgroup mysql
killall -9 mysql
killall -9 mysqld
apt-get remove –purge mysql-server mysql-client mysql-common
apt-get autoremove
apt-get autoclean
rm -rf /var/lib/mysql
Then re-install:
apt-get install mysql-server
ssh-keygen -R (FQDN)
Matt Long
3/31/2015
Connecting to a database with PHP
Connecting to a database with PHP
Install these packages:
#apt-get install apache2
#apt-get install mysql
#apt-get install php
#apt-get install php5-mysql
Create a test user, password and database
At the sql server, Log into mysql:
#mysql -u root -p
Issue the following commands to create a user “test” and a password “password”:
CREATE USER ‘test’@’localhost’ IDENTIFIED BY ‘password’;
CREATE USER ‘test’@’%’ IDENTIFIED BY ‘password’;GRANT ALL ON *.* TO ‘test’@’localhost’;
GRANT ALL ON *.* TO ‘test’@’%’;CREATE DATABASE instruments
Exit mysql:
q
Log back in as the user you just created, attaching to the new database:
mysql -u test -p instruments
Execute a
s
to see the status. Verify the user and database.
Test PHP Functionality:
Create a file named “something”.php and insert the following text:
<?php echo ‘hello world’.time();
/* <?php echo ‘mysqli_connect(); print_r(mysqli_query(‘select now()’)) ; ?> */
?>
Place this file in the /var/www directory
Open a browser and point to that file:
http://<your server>”something”.php
You should see hello world and the date.
To test your connection to the database via PHP:
Create a file with the following text and name it “something”.php
Edit the line “$db = mysql_connect(“206.207.94.34″,”test”,”password”);” to reflect your server & user.
<?php
$db = mysql_connect(“206.207.94.34″,”test”,”password”);
if (!$db) {
die(“Database connection failed miserably: ” . mysql_error());
}
elsedie(“Database Success!!!: ” . mysql_error());
$db_select = mysql_select_db(“instruments”,$db);
if (!$db_select) {
die(“Database selection also failed miserably: ” . mysql_error());
}
?>
<html>
<head>
<title>Step 3</title>
</head>
<body>
<?php
$result = mysql_query(“SELECT * FROM mytable”, $db);
if (!$result) {
die(“Database query failed: ” . mysql_error());
}
?>
</body>
</html>
Place this file in the /var/www directory
Open a browser and point to that file:
http://<your server>”something.php
Success!!!
HANDY MYSQL COMMANDS:
Note that all text commands must be first on line and end with ‘;’
? (?) Synonym for `help’.
clear (c) Clear the current input statement.
connect (r) Reconnect to the server. Optional arguments are db and host.
delimiter (d) Set statement delimiter.
edit (e) Edit command with $EDITOR.
ego (G) Send command to mysql server, display result vertically.
exit (q) Exit mysql. Same as quit.
go (g) Send command to mysql server.
help (h) Display this help.
nopager (n) Disable pager, print to stdout.
notee (t) Don’t write into outfile.
pager (P) Set PAGER [to_pager]. Print the query results via PAGER.
print (p) Print current command.
prompt (R) Change your mysql prompt.
quit (q) Quit mysql.
rehash (#) Rebuild completion hash.
source (.) Execute an SQL script file. Takes a file name as an argument.
status (s) Get status information from the server.
system (!) Execute a system shell command.
tee (T) Set outfile [to_outfile]. Append everything into given outfile.
use (u) Use another database. Takes database name as argument.
charset (C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (W) Show warnings after every statement.
nowarning (w) Don’t show warnings after every statement.
For server side help, type ‘help contents’
Matt Long
01/27/2015
Getting Started with Mysql and Heidi SQL
Getting Started with Mysql and Heidi SQL
At the target server prompt, become root and type:
apt-get install mysql-server
provide a a password for the root mysql user
Verify that mysql is running:
netstat -tap | grep mysql
you should see something like this:
tcp 0 0 localhost:mysql *:* LISTEN 21921/mysqld
Verify that you can log in:
mysql -u root -p
You should be prompted for the root password and your prompt should change to:
mysql>
Type “help” or “?” to review the commands.
Type to exit:
q
To configure Mysql for remote connections edit /etc/mysql/my.cnf
Verify the port number. The default is 3306
Set the bind-address to 0.0.0.0
Log in to mysql as above and execute the following commands.
CREATE USER ‘myuser’@’localhost’ IDENTIFIED BY ‘mypass’;
CREATE USER ‘myuser’@’%’ IDENTIFIED BY ‘mypass’;
GRANT ALL ON *.* TO ‘myuser’@’localhost’;
GRANT ALL ON *.* TO ‘myuser’@’%’;
Note: the ‘localhost’ and ‘%’ are the correct syntax. Only change myuser and mypass.
q
Restart mysql:
/etc/init.d/mysql restart
You should be able to install Heidi SQL and log in now.
Matt
01/23/2015
Working with GB Large mysql dump files -splitting insert statements
Working with GB Large mysql dump files -splitting insert statements
Recently I had to restore a huge database from a huge MySQL dump file.
Since the dump file was had all of the create statements mixed with insert statements, I found the recreation of the database to take a very long time with the possibility that it might error out and rollback all of the transactions.
So I came up with the following script which processes the single MySQL dump file and splits it out so we can run the different parts separately.
This creates files that can be run individually called
- mysql.tblname.beforeinsert
- mysql.tblname.insert
- mysql.tblname.afterinsert
cat mysql.dump.sql| awk 'BEGIN{ TABLE="table_not_set"}
{
if($1=="CREATE" && $2=="TABLE")
{
TABLE=$3
gsub("`","",TABLE)
inserted=false
}
if($1!="INSERT")
{
if(!inserted)
{
print $0 > "mysql."TABLE".beforeinsert";
}
else
{
print $0 > "mysql."TABLE".afterinsert";
}
} else {
print $0 > "mysql."TABLE".insert";
inserted=true
}
}
'
Great SQL Formatting Tool
Great SQL Formatting Tool
We often deal with very complex, dynamically generated SQL Statements which run from our applications.
If we need to debug them for any reason we often have to display them to the screen and then copy and paste them in to an SQL Query window. The problem is that those SQL Statements are not always formatted to be very readable. Sometimes they might even be on a single line. This requires a bunch of time going through and reformatting the sql statement, making it legible for debugging.
I have used this tool SQLinFORM several times in the past but I keep forgetting about when I dont have to use it very often.
http://www.sqlinform.com/
I just copy and paste the SQL into the window and click Format.
It does a great job formatting code quickly and even has some options for how you would like to see the output, I then select the output and paste it into my SQL Query window.
If you use it often they do have a version for sale.