.:. UCARP .:.
Documentation for version 1.2
------------------------ BLURB ------------------------
UCARP allows a couple of hosts to share common virtual IP addresses in order
to provide automatic failover. It is a portable userland implementation of the
secure and patent-free Common Address Redundancy Protocol (CARP, OpenBSD's
alternative to the patents-bloated VRRP).
Strong points of the CARP protocol are: very low overhead, cryptographically
signed messages, interoperability between different operating systems and no
need for any dedicated extra network link between redundant hosts.
Home page is http://www.ucarp.org/
------------------------ COMPILATION ------------------------
libpcap (http://www.tcpdump.org/) must be installed on your system, with
development files (headers).
Then, follow the boring traditional procedure:
./configure
make install-strip
For details, have a look at the INSTALL file.
The software has been successfully tested on Linux 2.4, Linux 2.6, MacOS X,
OpenBSD, MirBSD and NetBSD.
------------------------ REQUIREMENTS ------------------------
A couple of virtual hosts must be given:
- A shared virtual IP, which will be dynamically answered by one alive host.
Services that need high availability need to be assigned to that virtual IP.
- A real IP address for each host.
- A shared identifier for the virtual IP address, which is a number between 1
and 255.
- For each host : an advertisement time base and skew, which is the frequency
the host will tell the other one that it's still alive. By default, base is 1
and skew is 0, which basically means one advertisement a second. The protocol
is very light, a tiny packet every second won't have any noticeable impact on
your network.
- A shared password (that will never go plaintext to the network).
- A script to bring the virtual address up when a host becomes the master.
- Another script to bring the virtual address down when a host is no more the
master.
------------------------ USAGE ------------------------
The server will usually be installed as : /usr/local/sbin/ucarp
Everything is driven through command-line options.
In order to see the list of available options, try : /usr/local/sbin/ucarp -h
Better than a long technical discussion, here's a real-life setup example.
Your company has an internal mail relay whose IP address is 10.1.1.252. Every
user has configured his mail client with that host or IP address and the
service must always be up and running without reconfiguring mail clients in
case of a failure.
It's why you set up two mail servers hosts with an identical configuration.
Their real IP addresses are 10.1.1.1 and 10.1.1.2.
Let's see how to assign the same additionnal IP address (10.1.1.252) to both
servers, so that when one goes down, the other one goes up.
First, we will create a script that brings the virtual IP address up. Let's
save that file as /etc/vip-up.sh :
#! /bin/sh
/sbin/ip addr add 10.1.1.252/24 dev eth0
Now another script to bring it down, /etc/vip-down.sh :
#! /bin/sh
/sbin/ip addr del 10.1.1.252/24 dev eth0
Of course, anything can go in these scripts. For instance, you may want to add
routes, to add something to log files or to send mail. And last, but not
least, you can use a script that will connect to your switches and flush their
ARP cache. Some users reported that transitions were way faster when also
switching MAC addresses.
The interface name is passed as an argument to the called scripts, so feel
free to replace "eth0" with "$1" in the previous examples.
Don't forget to make those files executable :
chmod +x /etc/vip-up.sh /etc/vip-down.sh
Right. What we need now is an identifier for the virtual IP. Let's take "42".
And we also need a password. Let's take "love".
Now, on the first host (whoose real IP is 10.1.1.1), run :
/usr/local/sbin/ucarp -v 42 -p love -a 10.1.1.252 -s 10.1.1.1 &
On the second host, whose real IP is 10.1.1.2, run :
/usr/local/sbin/ucarp -v 42 -p love -a 10.1.1.252 -s 10.1.1.2 &
You should see that one of those hosts quickly becomes the master, and the
other one the backup. Related scripts are spawned on change.
Now unplug the master. After a few seconds, the other host becomes the new
master.
By changing the base (the -b switch) you can have a "preferred" master. The
lower the value is, the more likely it's going to be a master.
Please note that by default, and if everything's ok, a master will stay a
master as long as possible. If you want a "preferred" master to immediately
become a master even if another host is already the master:
- add the --preempt (or -P) switch to *all* hosts
- use a lower skew or a lower base for the "preferred" one.
When ucarp first runs, it starts as a backup and listens to the network to
determine if it should become the master. When it enters the backup state, it
normally runs the downscript. That can be useful to remove old temporary files
or clean up an interface that is in an unknown state. In some circumstances,
however, it is undesirable to run the downscript if the service was not
already up. In that case, use the --neutral (-n) switch for ucarp to not run
the downscript when it enters the backup state the first time. All changes
from the master state to the backup state after that will run the downscript.
--shutdown (-z) will run the downscript at exit, unless ucarp is already in
the backup state.
The "dead ratio" (--deadratio=...) knob basically changes how long a backup
server will wait for an unresponsive master before considering it as dead, and
becoming the new master. In the original protocol, the ratio is 3. This is
also the default when this command-line switch is missing.
Notices are sent both to stderr/stdout and to the syslog daemon (with the
"daemon" facility) by default. stderr/stdout are bypassed if the daemon is
started in background (--daemonize). Facilities can be changed with the
--syslog switch. Use --syslog=none to disable syslog logging, for instance if
prefer using something like multilog.
------------------------ TRANSLATIONS ------------------------
UCARP can speak your native language through gettext / libintl.
If you want to translate the software, have a look at the po/ directory.
Copy the ucarp.pot file to <your locale name>.po and use software like Kbabel
or Emacs to update the file.
Better use use your local charset than UTF-8.
------------------------ DOWNLOADING UCARP ------------------------
UCARP home page is: http://www.ucarp.org/ .
UCARP mailing-list: http://www.ucarp.org/ml/
Thank you,
Tuesday, October 22, 2013
Thursday, October 17, 2013
Netapp SAN Basic commands
SAN-Daily Health Check
filer> rdfile /etc/messages
Note: If you want to see old syslog messages, use following commands /etc/messages.0; /etc/messages.1 etc.,
filer> snapmirror status
filer> snapvault status
filer> vol status -f (or) aggr status -f
filer> environment chassis list-sensors
filer> vol status
filer> aggr status
filer> df -Ah
filer> df -h
filer> lun show offline
filer> sysconfig -a
filer> rdfile /etc/messages
Note: If you want to see old syslog messages, use following commands /etc/messages.0; /etc/messages.1 etc.,
filer> snapmirror status
filer> snapvault status
filer> vol status -f (or) aggr status -f
filer> environment chassis list-sensors
filer> vol status
filer> aggr status
filer> df -Ah
filer> df -h
filer> lun show offline
filer> sysconfig -a
Managing or monitoring or troubleshooting a Netapp filer.
- sysconfig -a : shows hardware configuration with more verbose information
- sysconfig -d : shows information of the disk attached to the filer
- version : shows the netapp Ontap OS version.
- uptime : shows the filer uptime
- dns info : this shows the dns resolvers, the no of hits and misses and other info
- nis info : this shows the nis domain name, yp servers etc.
- rdfile : Like “cat” in Linux, used to read contents of text files/
- wrfile : Creates/Overwrites a file. Similar to “cat > filename” in Linux
- aggr status : Shows the aggregate status
- aggr status -r : Shows the raid configuration, reconstruction information of the disks in filer
- aggr show_space : Shows the disk usage of the aggreate, WAFL reserve, overheads etc.
- vol status : Shows the volume information
- vol status -s : Displays the spare disks on the filer
- vol status -f : Displays the failed disks on the filer
- vol status -r : Shows the raid configuration, reconstruction information of the disks
- df -h : Displays volume disk usage
- df -i : Shows the inode counts of all the volumes
- df -Ah : Shows “df” information of the aggregate
- license : Displays/add/removes license on a netapp filer
- maxfiles : Displays and adds more inodes to a volume
- aggr create : Creates aggregate
- vol create
: Creates volume in an aggregate - vol offline
: Offlines a volume - vol online
: Onlines a volume - vol destroy
: Destroys and removes an volume - vol size
[+|-] : Resize a volume in netapp filer - vol options : Displays/Changes volume options in a netapp filer
- qtree create
: Creates qtree - qtree status : Displays the status of qtrees
- quota on : Enables quota on a netapp filer
- quota off : Disables quota
- quota resize : Resizes quota
- quota report : Reports the quota and usage
- snap list : Displays all snapshots on a volume
- snap create
: Create snapshot - snap sched
: Schedule snapshot creation - snap reserve
: Display/set snapshot reserve space in volume - /etc/exports : File that manages the NFS exports
- rdfile /etc/exports : Read the NFS exports file
- wrfile /etc/exports : Write to NFS exports file
- exportfs -a : Exports all the filesystems listed in /etc/exports
- cifs setup : Setup cifs
- cifs shares : Create/displays cifs shares
- cifs access : Changes access of cifs shares
- lun create : Creates iscsi or fcp luns on a netapp filer
- lun map : Maps lun to an igroup
- lun show : Show all the luns on a filer
- igroup create : Creates netapp igroup
- lun stats : Show lun I/O statistics
- disk show : Shows all the disk on the filer
- disk zero spares : Zeros the spare disks
- disk_fw_update : Upgrades the disk firmware on all disks
- options : Display/Set options on netapp filer
- options nfs : Display/Set NFS options
- options timed : Display/Set NTP options on netapp.
- options autosupport : Display/Set autosupport options
- options cifs : Display/Set cifs options
- options tcp : Display/Set TCP options
- options net : Display/Set network options
- ndmpcopy
: Initiates ndmpcopy - ndmpd status : Displays status of ndmpd
- ndmpd killall : Terminates all the ndmpd processes.
- ifconfig : Displays/Sets IP address on a network/vif interface
- vif create : Creates a VIF (bonding/trunking/teaming)
- vif status : Displays status of a vif
- netstat : Displays network statistics
- sysstat -us 1 : begins a 1 second sample of the filer’s current utilization (crtl – c to end)
- nfsstat : Shows nfs statistics
- nfsstat -l : Displays nfs stats per client
- nfs_hist : Displays nfs historgram
- statit : beings/ends a performance workload sampling [-b starts / -e ends]
- stats : Displays stats for every counter on netapp. Read stats man page for more info
- ifstat : Displays Network interface stats
- qtree stats : displays I/O stats of qtree
- environment : display environment status on shelves and chassis of the filer
- storage show
: Shows storage component details - snapmirror intialize : Initialize a snapmirror relation
- snapmirror update : Manually Update snapmirror relation
- snapmirror resync : Resyns a broken snapmirror
- snapmirror quiesce : Quiesces a snapmirror bond
- snapmirror break : Breakes a snapmirror relation
- snapmirror abort : Abort a running snapmirror
- snapmirror status : Shows snapmirror status
- lock status -h : Displays locks held by filer
- sm_mon : Manage the locks
- storage download shelf : Installs the shelf firmware
- software get : Download the Netapp OS software
- software install : Installs OS
- download : Updates the installed OS
- cf status : Displays cluster status
- cf takeover : Takes over the cluster partner
- cf giveback : Gives back control to the cluster partner
- reboot : Reboots a filer
Here are some of the useful functions of “storage” command in Netapp.
1) To show all disks on the system : Use “storage show disk -T” to display all the disks attached to the filer, the disk serial number, vendor, model, disk firmware version and type of disk (SATA/ATA/FCAL)
# rsh filer12 storage show disk -T
DISK SHELF BAY SERIAL VENDOR MODEL REV TYPE
——————— ——— —————- ——– ———- —- ——
0d.16 1 0 xxxxxxxxxxxxxxxx NETAPP X276 NA07 FCAL
0d.17 1 1 xxxxxxxxxxxxxxxx NETAPP X276 NA07 FCAL
0d.18 1 2 xxxxxxxxxxxxxxxx NETAPP X276 NA07 FCAL
0d.19 1 3 xxxxxxxxxxxxxxxx NETAPP X276 NA07 FCAL
0d.20 1 4 xxxxxxxxxxxxxxxx NETAPP X276 NA07 FCAL
0d.21 1 5 xxxxxxxxxxxxxxxx NETAPP X276 NA07 FCAL
0d.22 1 6 xxxxxxxxxxxxxxxx NETAPP X276 NA07 FCAL
…
DISK SHELF BAY SERIAL VENDOR MODEL REV TYPE
——————— ——— —————- ——– ———- —- ——
0d.16 1 0 xxxxxxxxxxxxxxxx NETAPP X276 NA07 FCAL
0d.17 1 1 xxxxxxxxxxxxxxxx NETAPP X276 NA07 FCAL
0d.18 1 2 xxxxxxxxxxxxxxxx NETAPP X276 NA07 FCAL
0d.19 1 3 xxxxxxxxxxxxxxxx NETAPP X276 NA07 FCAL
0d.20 1 4 xxxxxxxxxxxxxxxx NETAPP X276 NA07 FCAL
0d.21 1 5 xxxxxxxxxxxxxxxx NETAPP X276 NA07 FCAL
0d.22 1 6 xxxxxxxxxxxxxxxx NETAPP X276 NA07 FCAL
…
2) To see complete information of a particular disk : Use “storage show disk -a ” to view complete information of a netapp disk. This command gives you the shelf, bay, serial number of disk, disk speed and many other.
# rsh filer12 storage show disk -a 0d.99
Disk: 0d.99
Shelf: 5
Bay: 13
Serial: xxxxxxxxxxxxxxxxxxxx
Vendor: NETAPP
Model: X276
Rev: NA07
RPM: 10000
WWN: xxxxxxxxxxxxxxxxxxa
UID: xxxxxxxxxxxxxxxxx:00000000:00000000:00000000:00000000
Downrev: no
Pri Port: B
Power-on Hours: N/A
Blocks read: 0
Blocks written: 0
Time interval: 00:00:00
Glist count: 0
Scrub last done: 00:00:00
Scrub count: 0
LIP count: 0
Dynamically qualified: No
#
Disk: 0d.99
Shelf: 5
Bay: 13
Serial: xxxxxxxxxxxxxxxxxxxx
Vendor: NETAPP
Model: X276
Rev: NA07
RPM: 10000
WWN: xxxxxxxxxxxxxxxxxxa
UID: xxxxxxxxxxxxxxxxx:00000000:00000000:00000000:00000000
Downrev: no
Pri Port: B
Power-on Hours: N/A
Blocks read: 0
Blocks written: 0
Time interval: 00:00:00
Glist count: 0
Scrub last done: 00:00:00
Scrub count: 0
LIP count: 0
Dynamically qualified: No
#
3) To list all storage adapters on the filer : Use “storage show adapter -a” command to display all the storage adapters (hba) on the filer.
# rsh filer12 storage show adapter -a
Slot: 0a
Description: Fibre Channel Host Adapter 0a (Dual-channel, QLogic 2322 rev. 3)
Firmware Rev: 3.3.25
FC Node Name: xxxxxxxxxxxxxxxxxxx
FC Packet Size: 2048
Link Data Rate: 2 Gbit
SRAM Parity: Yes
External GBIC: No
State: Enabled
In Use: No
Redundant: Yes
Description: Fibre Channel Host Adapter 0a (Dual-channel, QLogic 2322 rev. 3)
Firmware Rev: 3.3.25
FC Node Name: xxxxxxxxxxxxxxxxxxx
FC Packet Size: 2048
Link Data Rate: 2 Gbit
SRAM Parity: Yes
External GBIC: No
State: Enabled
In Use: No
Redundant: Yes
Slot: 0b
Description: Fibre Channel Host Adapter 0b (Dual-channel, QLogic 2322 rev. 3)
Firmware Rev: 3.3.25
FC Node Name: xxxxxxxxxxxxxxxxxxx
..
Description: Fibre Channel Host Adapter 0b (Dual-channel, QLogic 2322 rev. 3)
Firmware Rev: 3.3.25
FC Node Name: xxxxxxxxxxxxxxxxxxx
..
4) To get shelf details of filer : Use “storage show shelf ” command to display the details of the shelf and its partner shelf.
# rsh filer12 storage show shelf 0c.shelf2
Shelf name: 0c.shelf2
Channel: 0c
Module: A
Shelf id: 2
Shelf UID: xxxxxxxxxxxxxxxxxxxxxxx
Term switch: N/A
Shelf state: ONLINE
Module state: OK
Shelf name: 0c.shelf2
Channel: 0c
Module: A
Shelf id: 2
Shelf UID: xxxxxxxxxxxxxxxxxxxxxxx
Term switch: N/A
Shelf state: ONLINE
Module state: OK
Loop Invalid Invalid Clock Insert Stall Util LIP
Disk Disk Port up CRC Word Delta Count Count Percent Count
Id Bay State Count Count Count
—————————————————————————————-
[IN ] OK 0 0 0 8 0 0 71 0
[OUT ] OK 0 0 0 0 0 0 52 0
[ 32] 0 OK 0 0 0 32 0 0 0 0
[ 33] 1 OK 0 0 0 32 0 0 2 0
[ 34] 2 OK 0 0 0 24 0 0 0 0
[ 35] 3 OK 0 0 0 24 0 0 1 0
[ 36] 4 OK 0 0 0 8 0 0 2 0
[ 37] 5 OK 0 0 0 24 0 0 4 0
…
Disk Disk Port up CRC Word Delta Count Count Percent Count
Id Bay State Count Count Count
—————————————————————————————-
[IN ] OK 0 0 0 8 0 0 71 0
[OUT ] OK 0 0 0 0 0 0 52 0
[ 32] 0 OK 0 0 0 32 0 0 0 0
[ 33] 1 OK 0 0 0 32 0 0 2 0
[ 34] 2 OK 0 0 0 24 0 0 0 0
[ 35] 3 OK 0 0 0 24 0 0 1 0
[ 36] 4 OK 0 0 0 8 0 0 2 0
[ 37] 5 OK 0 0 0 24 0 0 4 0
…
Wednesday, October 16, 2013
Apache SSL
HTTPS Configuration
HTTPS load balancing requires an SSL certificate and the associated key pair. This page describes how to set it up .
Note that in https offload pools client-side traffic uses https and server-side traffic uses http. For additional information on https offload pools, please check Load Balancing Pool Types page.
Note that in https offload pools client-side traffic uses https and server-side traffic uses http. For additional information on https offload pools, please check Load Balancing Pool Types page.
Note also that pool type can't be changed from http to https by just changing its port value via manage-lb-pool modify command. You have to create a new pool with the type specified as https (refer to manage-lb-pool man page for command options details).
1. Start an Instance
Reserve an available server and launch an instance as usual via manage-server reserve and manage-instance launch commands (see manage-server and manage-instance wiki man pages). When the instance status has changed from "starting" to "running," log in to the instance in a usual way:
(It's supposed here that you use key.pem as your private key file to login to your instances).
2. Install and Configure Your Web Server
Installing Apache (package name is "httpd"):
Make Apache start automatically on reboot:
Let's create a dummy web page on the server to check that our installation works:
Now installing SSL module for Apache (mod_ssl):
3. Configure SSL Using Self-signed Certificate
(For a certificate signed by a certificate authority, see section 4. Configure SSL Using Authorized Certificate and Certificate Chains below.)
We have to alter the SSL configuration by editing /etc/httpd/conf.d/ssl.conf file (installed with mod_ssl module) to make the web server aware of the secure certificate.
We also need to generate a Certificate Authority (CA) key pair.
Here are basic steps we should perform to create a self-signed certificate:
Here are basic steps we should perform to create a self-signed certificate:
1. Creating RSA key and certificate request:
After setting the passphrase and answering to several questions, we got two files – cert.csr privkey.pem
2. Removing passphrase from the key (need to enter passphrase set on step 1.)
3. Converting request into a signed certificate:
(Use whatever number of days you need.)
4. Now we have a private key. Let's set the correct permissions for it:
5. Moving certificates to the place they belong:
Configuring the basics in ssl.conf:
The path and filename /etc/httpd/conf.d/ssl.conf are correct for CentOS5.
Starting web server:
Next we need temporarily open port 443 to our instance via manage-vlan tool (check How to Set Firewall Rules for details).
Let's query instance via port 443 directly (no load balancing yet).
Let's query instance via port 443 directly (no load balancing yet).
Using -k option to let curl perform an "insecure" SSL connection using our self-signed certificate.
4. Configure SSL Using Authorized Certificate and Certificate Chains
This is not much different from the previous step except we already have a signed certificate (server.crt), key (server.key), and chain certificate (intermediate_bundle.crt) issued by some Certification Authority.
Let's check if passphrase is removed from the key file (the AppNexus API does not currently support passphrase encrypted private keys):
If the key was created with a passphrase, prompt to enter it will appear. Then removing the passphrase as follows:
Entering existing passphrase when prompted. Now we got new key file without a passphrase. The original key file remained unchanged.
Setting permissions:
Movinging certificates and key to the right place:
Configure the basics in ssl.conf on your instance:
(The path and filename /etc/httpd/conf.d/ssl.conf are correct for CentOS 5.)
Starting web server:
Load Balancing Method
The load balancing method is the logic a load balancer uses to route traffic to pool members. Note that the LTM will balance TCP connections and not individual requests. This means that if you select, say, "Round Robin" as a load-balancing method and load a URL in a browser you will continue to hit the same node until you start a new TCP session.
| Round Robin | This is the default load balancing method. The Round Robin method passes each new connection request to the next server in line, eventually distributing connections uniformly across the array of machines being load balanced. Round Robin works well in most configurations, especially if the equipment that you are load balancing is roughly equal in processing speed and memory. |
|---|---|
| Fastest Node | The Fastest Node method passes a new connection based on the fastest response of all currently active nodes. This method may be particularly useful in environments where nodes are distributed across different logical networks. |
| Least Connections | The Least Connections method passes a new connection to the node that has the least number of current connections. Least Connections works best in environments where the equipment you are load balancing has similar capabilities. |
| Observed Connections | The Observed Connections method uses a combination of the logic used in the Least Connections and Fastest methods. Nodes are ranked based on a combination of the number of current connections and the response time. Observed Connections works well in any environment, but may be particularly useful when node performance varies significantly. |
AB tool
Performance Testing your Web Server
To benchmark the performance of your web server applications we recommend the Apache "ab" tool. The ab tool will show how many requests per second your Apache installation is capable of serving. The ab tool is a part of the Apache httpd package in CentOS and Red Hat distributions and the "apache2-utils" package in Debian.
Below is the basic ab command and its output. The -c parameter specifies the number of connections; the -k stands for HTTP Keep-Alive; and the -tparameter sets the time in seconds for which each connection is alive. The application is then hammered through those connections.
- To perform a "flood" test we set the number of requests (-n) to, say, 5000, and assign the number of concurrent connections{{ (-c}}) to something like 200:
- If the ab output makes you suspect issues, it is useful to look into any replies using tcpdump. In particular, tcp-rst replies could appear. To catch them, use:
- We are interested mostly in tcp-rst server replies, as they point to misconfiguration or performance issues. To catch server-side tcp-rst replies use:
where 89.253.250.50 is the server hosting your tests.
Friday, October 11, 2013
Tuesday, October 8, 2013
Mysql Basic cmd
MySql administaertion on linux (rhel)
Install and Configure the MySQL on RHEL5:
#yum install *mysql*
#service mysqld restart
To defining SQL Admin and Password:
#mysqladmin -h <hostname> -u <username> password <xxxx>
Example:
#mysqladmin -h redhat.logixsi2.com -u root password redhat
Or
#mysqladmin -u root password redhat
To login (from unix shell)
# mysql -h hostname -u root -p
Or
#mysql -u root -p
Change a users password from unix shell:
# mysqladmin -u username -h hostname.blah.org -p password new-password
#mysqladmin -u root -h redhat.logixsi2.com -p password redhatadmin
Password: (Provide here old mysql password of the user)
To login (from unix shell)
#mysql ¡Vh hostname -u root -p
# mysql ¡Vh redhat.logixsi2.com -u root ¡Vp
To show the MySQL version information:
List all databases on the SQL server:
mysql> show databases;
Create a database on the SQL server:
mysql> create databases [databasesname];
mysql> create databases school;
Switch to a database:
mysql> use [db name];
mysql> use school;
To see all the tables in the db:
mysql> show tables;
Create a table in database:
mysql> create table students (IDnumber int(10), name varchar(100), class varchar(10), marks
int(100), rank int(10));
Example : To Create a Table
mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middlenameVARCHAR(3), lastnameVARCHAR(35),office VARCHAR(10),useridVARCHAR(15),username VARCHAR(8),emailVARCHAR(35),phone VARCHAR(25),date_of_join VARCHAR(15));
To see database's field formats in the table:
mysql> describe <table name>;
To Insert values into tables:
mysql> insert into <table name> values (1, ‘shiva’,’8th’, ‘99’, ‘1st’);
Show all data in a table:
mysql> SELECT * FROM [table name];
Show specific columns by listing their names:
To delete a table:
mysql> drop table <table name>;
mysql> drop table students;
To delete a database:
mysql> drop database <database name>;
mysql> drop database school;
Delete a row(s) from a table:
mysql> DELETE from [table name] where [field name] = 'whatever';
mysql> DELETE FROM students WHERE name="kumar";
To modify or update entries in the table use the UPDATE command:
mysql> UPDATE students SET marks=”77” WHERE name=”shiva¡”;
Add a column into DB:
mysql> alter table [table name] add column [new column name] varchar (20);
Delete a column:
mysql> alter table [table name] drop column [column name];
Show certain selected rows with the value "whatever":
mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";
Change column name:
mysql> alter table [table name] change [old column name] [new column name] varchar (50);
Make a column values modify:
mysql> alter table [table name] modify [column name] VARCHAR(3);
Make a unique (Key) column so you get no dupes.
mysql> alter table [table name] add unique ([column name]);
Delete unique from table.
mysql> alter table [table name] drop index [colmn name];
Update database permissions/privileges.
mysql> flush privileges;
Creating a new user
Login as root à Switch to the MySQL db à Make the user & Update privs.
# mysql -u root ¡Vp
mysql> use mysql;
mysql> INSERT INTO
user(Host,User,Password)VALUES('%','username',PASSWORD('password'));
mysql> flush privileges;
mysql> select user, host, password from user where user=¡¦user-name¡¦;
Change a user¡¦s password from MySQL prompt.
Login as root à Set the password. Update privs.
# mysql -u root ¡Vp
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
mysql> flush privileges;
Recover MySQL root password.
Stop the MySQL server process è Start again with no grant tables è Login to MySQL as
root è Set new password è Exit MySQL and restart MySQL server.
# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start
Allow the user "bob" to connect to the server from localhost using the password "passwd".
Login as root è Switch to the MySQL db èGive privs èUpdate privs.
# mysql -u root ¡Vp
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by 'passwd';
mysql> flush privileges;
Give user privileges for a db.
Login as root è Switch to the MySQL db èGrant privs & Update privs.
# mysql -u root ¡Vp
mysql> use mysql;
mysql> INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> flush privileges;
or
mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;
Back Up and Restore of MySQL Database
Taking backup of specified ¡§database¡¨ :
# mysqldump --opt -u [username] -p[passwd] [dbname] > backupfile.sql
[uname] Your database username
[pass] The password for your db (note there is no space between -p and the password)
[dbname] The name of your database
[backupfile.sql] The filename for your database backup
[--opt] The mysqldump option
# mysqldump --opt -u root -pmysql movies > moviesbackup.sql
Taking backup of certain tables from the database:
# mysqldump -u [username] -p[passwd] dbname table1 table2 > backup.sql
Example: Taking back up only ¡§user and host¡¨ tables from the ¡§mysql¡¨ database:
Taking backup more than one database at once:
#mysqldump -u [username] -p --databases db1 db2 db3 > dbs_backup.sql
Taking back up of all the databases in the server at one time:
# mysqldump -u [username] -p[passwd] --all-databases > alldb_backup.sql
Back up your MySQL Database with Compress:
# mysqldump -u [uname] -p[passwd] [dbname] | gzip -9 > backupfile.sql.gz
Restoring your MySQL Database:
#mysql -u [username] -p [dbname] < backup.sql
To restore compressed backup files you can do the following:
#gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[passwd] [dbname]
Backing Up and Restoring using PHPMyAdmin
To backup your MySQL database using PHPMyAdmin just follow a couple of steps:
. Open phpMyAdmin.
. Select your database by clicking the database name in the list on the left of the screen.
. Click the Export link. This should bring up a new screen that says View dump of database (or
something similar).
. In the Export area, click the Select All link to choose all of the tables in your database.
. In the SQL options area, click the right options.
. Click on the Save as file option and the corresponding compression option and then click the 'Go'
button. A dialog box should appear prompting you to save the file locally.
Restoring your database is easy as well as backing it up. Make the following:
. Open phpMyAdmin.
. Create an appropriately named database and select it by clicking the database name in the list on
the left of the screen. If you would like to rewrite the backup over an existing database then click
on the database name, select all the check boxes next to the table names and select Drop to delete
all existing tables in the database.
. Click the SQL link. This should bring up a new screen where you can either type in SQL
commands, or upload your SQL file.
. Use the browse button to find the database file.
. Click Go button. This will upload the backup, execute the SQL commands and re-create your
database.
MySQL Configuration File:
#vi /etc/my.cnf
To check the logs info:
#mysqlbinlog <log-file>
Subscribe to:
Posts (Atom)