MySQL User Privileges Migration

During the MySQL version upgrade or for easy user backup you can you below.
 
To export all MySQL user privileges run following script.
  
mysql -h {host_name} -u {user_name} -p{password} -Ne "select distinct concat( \"SHOW GRANTS FOR '\",user,\"'@'\",host,\"';\" ) from user;" mysql | mysql -h {host_name} -u {user_name} -p{password} | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'

Note : Replace {host_name}, {user_name} and {password} with your values.

Above script will generate all grants statements.You can then take that output and run the statements against MySQL on the new server.

Comments

Post a Comment

Popular posts from this blog

How do I Use the Linux Top Command?

The Unix top command is designed to help users determine which processes are running and which applications are using more memory or processing power than they should be. The top command is very easy to use but you should know the things in details. The output of to is : top output: top - 22:09:08 up 14 min, 1 user, load average: 0.21, 0.23, 0.30 Tasks: 81 total, 1 running, 80 sleeping, 0 stopped, 0 zombie Cpu(s): 9.5%us, 31.2%sy, 0.0%ni, 27.0%id, 7.6%wa, 1.0%hi, 23.7%si, 0.0%st Mem: 255592k total, 167568k used, 88024k free, 25068k buffers Swap: 524280k total, 0k used, 524280k free, 85724k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 3166 apache 15 0 29444 6112 1524 S 6.6 2.4 0:00.79 httpd 3161 apache 15 0 29444 6112 1524 S 5.9 2.4 0:00.79 httpd 3164 apache 15 0 29444 6112 1524 S 5.9 2.4 0:00.75 httpd 3169 apache 15 0 29444 6112 1524 S 5.9 2.4 0:00.74 httpd 3163 apache 15
Read more

IOPS measurement

Image
IOPS (input/output operations per second) is the standard unit of measurement for the maximum number of reads and writes to non-contiguous storage locations of devices like hard disk drives (HDD), solid state drives (SSD), and storage area networks (SAN). IOPs is often measured with an open source network testing tool called an Iometer. Some commonly accepted averages for random IO operations, calculated as 1/(seek + latency) = IOPS:  IOPS calculations Every disk in your storage system has a maximum theoretical IOPS value that is based on a formula. Disk performance — and IOPS — is based on three key factors: Rotational speed (aka spindle speed). Measured in revolutions per minute (RPM), most disks you'll consider for enterprise storage rotate at speeds of 7,200, 10,000 or 15,000 RPM with the latter two being the most common. A higher rotational speed is associated with a higher performing disk. This value is not used directly in calculations, but it is highly i
Read more