Re: Upgrade Mysql
Hi, I don't know what Linux distro you're using, but I'd make a backup of /var/lib/mysql dir before you do anything (in case the mysql package decides to nuke your stuff). If you have a dump of your dbs, that's fine too. And maybe a backup of your my.cnf. Just install the new mysql package, then start it. Then you'll need to run mysqlupgrade. Depending on the size of your database and type of tables you are using it can take a while. For InnoDB tables, for example, upgrade simply means copy to tmp table... that's really slow if you have a large table. Once mysqlupgrade runs without a hitch, you should be back in business. -Paul Webmaster Studio Informatica wrote: Hi, I need to upgrade Mysql 4 to Mysql 5 on Linux. I will uninstall version 4 and install version 5. With uninstallation usually database files remain in /var/lib/mysql/ I want to know if with the installation of Mysql 5 those database will be recognized and imported to work with the new version automatically. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Setup a replication slave without stopping master
Jed Reynolds wrote: If you are using LVM, you might consider snapshotting, however, doing a live snapshot without stopping mysql server would only work if you were copying only myisam tables. Mysql-hot-copy would probably be better, but either way, you need to flush your tables, which will briefly lock them, so they can get onto disk. In contrast, InnoDB actually needs to shut down to cleanly close its table structures before you can physically copy the filesystem. If you can do an LVM snapshot on the dir(s) holding InnoDB files, then you should actually be able to do a live backup. Once you restore from the snapshot on a different host, mysql will behave as if it's recovering from a crash. Then you can tell from the .err file where the last binlog position was: InnoDB: Last MySQL binlog file position 0 1574672, file name /blah/blah/mysql_binlog/binlog.091206 Then you can use mysqlbinlog to apply binlogs until you are caught up. The caveat is, again, that you have to do a snapshot on the entire innodb_data_home_dir and innodb_log_group_home_dir. Hence both InnoDB data file and log. This approach is known to work with Solaris ZFS and should work the same way with LVM. -Paul Choi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Error Number 1045 Access denied
If you are desperate, you can also start mysqld with --skip-grant-tables option. Then use mysql, update root password using the update statement that Jim wrote about. -Paul On Mon, 2008-08-18 at 17:25 +0100, Andy Shellam wrote: FYI, I don't think this was sent to the list, forgive me if it was and I didn't see it. Andy -- You probably don't have to reinstall it. If you never gave root a password, just login as Andy says below and, when prompted for the password, just press the ENTER key. Unless you gave root a password and forgot it, you should find yourself in the mysql program. Enter something like: update mysql.user set password = password('new pwd') where user = 'root'; flush privileges; There are other ways to change a pwd, but I use that since it's easier for me to remember one way. Jim On Mon, Aug 18, 2008 at 3:07 AM, Andy Shellam [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hi Andrew, I think he means re-install MySQL and provide a root password (you said when you originally installed it that you didn't give it a password.) After you've done this, try mysql from the command-line explicitly telling it to use the user root: mysql --user=root --password The --password here tells mysql to prompt you for a password, or alternatively you can provide the password on the command-line too: mysql --user=root --password=password Once you can login via the command-line there's no reason why the Admin GUI shouldn't be able to login too. Regards, Andy Quoting AndrewMcHorney [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]: How does one done this? At 08:41 AM 8/17/2008, Sreekanth CHAVA wrote: Hi Andrew As per your Question , you can test with the below solution :- Try to reconfigure MySQL by providing the root password and then try to run the administer GUI. This should solve the problem. CHAVA On Sat, Aug 16, 2008 at 10:22 PM, AndrewMcHorney [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]wrote: Hello I am still getting the MySQL Error Number 1045 Access denied error message when running the adminstrator gui. I attempted to start mysql from the dos command line via mysql and I am getting the following. ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO). I am logging in as root, locahost and no password because I did not enter one when I installed the software. I will be changing that. For the command line I just entered mysql and a return. How can I fix this. I am off for the week and I would like to get this working so I can create a database or 2. Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Sreekanth CHAVA No virus found in this incoming message. Checked by AVG. Version: 7.5.524 / Virus Database: 270.6.4/1616 - Release Date: 8/16/2008 5:12 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Andy Shellam Business Systems Architect Network Mail NetServe Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul Choi [EMAIL PROTECTED]
Re: CHANGE MYSQL DATADIR TO ANOTHER HARDISK
Better yet, either do a cp -a (if GNU cp) or mv, then modify your my.cnf to reflect the new location. Your file permissions and ownership will have been preserved if you used either of these commands, and it's good to go. On Mon, 2008-08-18 at 11:59 +0530, Ananda Kumar wrote: Shut down mysql, move all the datafiles to /data and create a symbolic link from the current directory to /data. This should work. regards anandkl On 8/18/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi all, On my debian system, i have two hardisk. I want to change the mysql datadir to another hardisk (/dev/cciss/c0d1p1 /data ). Please tell me how to do this. /dev/cciss/c0d0p1 34G / /dev/cciss/c0d1p1 68G /data -- Krishna Chandra Prajapati -- Paul Choi [EMAIL PROTECTED]
Re: MYSQL PROCESS
27.1 324124 262548 ? SJun05 2:51 /usr/sbin/mysqld --basedir=/usr --datadir=/var/ mysql 5400 0.0 27.1 324124 262548 ? SJun05 3:35 /usr/sbin/mysqld --basedir=/usr --datadir=/var/ -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED] This message has been scanned for viruses by MailControl - (see http://bluepages.wsatkins.co.uk/?6875772) This email and any attached files are confidential and copyright protected. If you are not the addressee, any dissemination of this communication is strictly prohibited. Unless otherwise expressly agreed in writing, nothing stated in this communication shall be legally binding. The ultimate parent company of the Atkins Group is WS Atkins plc. Registered in England No. 1885586. Registered Office Woodcote Grove, Ashley Road, Epsom, Surrey KT18 5BW. A list of wholly owned Atkins Group companies registered in the United Kingdom can be found at http://www.atkinsglobal.com/terms_and_conditions/index.aspx Consider the environment. Please don't print this e-mail unless you really need to. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Paul Choi [EMAIL PROTECTED]
Re: Dismal performance on a 16G memory/8 core server - my.cnf settings?
I think what we need to know is more stuff about the database itself. How big is it? I assume if you were able to serve it from a Mac Mini it can't be all that big. 16GB should be big enough to contain all the data and serve it up quickly. And while 4-disk RAID 5 isn't all that great, it's certainly better than a single ATA disk in Mac Mini. If your database is huge, then 16GB is barely enough to hold innodb buffer pool (if innodb) and maybe the indexes. Then your RAID config will come into play (and maybe filesystem type, tuning). Then whether it's a Dell PE1750 or a 6650 would not make a huge difference since it's I/O bound. Were you testing with something like production data or just some test data? Have you modified my.cnf to reflect the new hardware config? Like Baron Schwartz asked, does your test reflect real-life workload? And yes, the 3.0Ghz Xeon processors you mentioned are 32-bit. You get that error message if your processor doesn't have EM64T capability. -Paul JW wrote: Hello, We recently purchased a Dell PowerEdge 6650 thinking it would be a real fast server. Specs are: OS: Linux Debian 4.0/Etch RAID 5 on 4x U320 15k rpm drives (uses a perc-raid 3/DC hardware raid controller) 16GB of RAM 4 3.0 Ghz Xeon processors - I think they're dual core, in /proc/cpuinfo it shows up as 8 processors - maybe it's only HT I first made the mistake of using the default kernel, which provides SMP support but not large memory support. I have the output of a mysql sql-bench run from mysql on a Mac Mini to compare performance with. The server was only 0.35 (relative) the speed of the Mac mini - that means an 8 core 3.0 Ghz Xeon server with 16GB of RAM was only about 3x as fast as a as a single-core 1.25 Ghz G4 with 1GB of RAM (and a mini uses those little laptop hard drives, too). Needless to say my employer was shocked at the terrible performance and decided to sell the 6650 right away. But I can't help but wonder if there's not something terribly wrong with the settings - either the OS or mysql settings. I changed the kernel to the -bigmem kernel. It now sees all the RAM, but the sql-bench output on this try was _exactly_ the same: 0.35 I copied the my-huge.cnf from the examples directory and changed the thread_concurrency setting to 8 (because it said to set it to No. of CPUs*2). I also set the tmpdir, basedir, datadir and language, which were set in the original my.cnf I ran sql-bench again and the performance was even worse this time: 0.36 Someone suggested I try the -amd64 kernels which provide 64 bit but when I try to boot it I get various errors about this CPU does not support long (something) please use a 32-bit OS - the 64 bit install CD says the same message. So I assume these are not 64 bit CPUs. Any idea how I can configure this server to maximize performace? I think the multiple CPUs are a waste: I'm not looking for lots of concurrency, I want 1 query done really fast. Thanks. JW -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading from 4.1 to 5.0
Does anyone have experience with upgrading large databases (~500GB each) from MySQL 4.1 to 5.0? The tables are in InnoDB format. We are using the Community version. I've read that it's recommended that you use mysqldump and then restore, but this is not possible for us, as we cannot have our databases down for long, nor can we have our tables locked while doing dump. I've tried doing the following steps: ibbackup --restore copy over mysql table dirs. set default char set to latin1 (or will default to utf8) in my.cnf because that's the original char set in 4.1 Upgrade only mysql database (user and privilege tables) mysqlcheck --check-upgrade --auto-repair mysql mysql_fix_privilege_tables I've written a script to compare data between the original 4.1 and the new 5.0. Looks like certain rows have different numerical data... so this is not good. I didn't want to do mysql_upgrade on all databases in this instance of MySQL because that resulted in 2 things happening: 1) Don't set default char set to latin1. Run mysql_upgrade Some rows had data truncated in certain columns. 2) Set default char set to latin1. Run mysql_upgrade Copies to TMP table. Takes forever... This is unacceptable for us. What is the recommended way to upgrade from 4.1 to 5.0? Or are we stuck using 4.1 forever? -Paul Choi Plaxo, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]